VBA UserForms in Excel

[MarkusLAndersson]

Hi

I would like some help wit a UserForm that I am about to create. The UserForm is suppose to present a list for the user in a ListBox and the user is then to select one row. Depending on the selection, data will be transferred from Sheet3 to Sheet1 in my excel document. In my user form I have to buttons, one OK button and one Cancel. The OK will make the transfer and the Cancel will cancel the UserForm. Below you can se my coda so far. I can read in data to my ListBox from Sheet3 but how am I suppose to get the selected data to sheet one. As you might notice I am totally new to using VBA in excel.

-----------------------------------------------------------
Private Sub CommandButton1_Click()

End Sub
-----------------------------------------------------------
Private Sub CommandButton2_Click()

End Sub
-----------------------------------------------------------
Private Sub UserForm_Initialize()
Dim Lrange As Range
Dim Larray() As Variant
Dim x As Variant
Dim ctr As Integer

'Set the range to loop through
Set Lrange = Sheet3.Range("A2:A53")

'Loops through the ranges
For Each x In Lrange

ReDim Preserve Larray(ctr)

'Add an item to the array
Larray(ctr) = x.Value

ctr = ctr + 1

Next x

'Assign the array to the listbox
ListBox1.List = Larray

End Sub
-----------------------------------------------------------

 

[ab123456]

At the moment the clicking of the buttons doesnt run any code. You need some code in the sub commandbutton1_click(), something like

Sheets("sheet1").Cells(8, 1).Value = ListBox1.Value

 

[MarkusLAndersson]

Thanks I managed to do just that just before you’re reply. As a follow up question I am wondering if it is possible to write the value from another sell in into Cell(8,2).

In my case the data in ListBox1 comes from colum 1 in Sheet3
Can I with the code from above get the data from colum 2 (and same row as ListBox1) in Sheet3 in to cell(8,1)

 

[ab123456]

Firstly you need to find which row the result of your listbox is in.

If the data in the listbox is the same order as the data on your sheet you can use the listindex property. This would return 0 if the first item in the list is selected, 1 if the second item is selected and so on. Then a line of code similar to that used earlier to write the value to the cell

The code would be something like below

row_number = ListBox1.ListIndex
sheets("sheet1").cells(8,1).value = sheets("sheet2").cells(row_number,2).value

Remember this will only work if the order of the data in the list box is the same as the original sheet.

 

[ab123456]

correction to above post

As the listindex returns 0 for item at top of list but your original data only starts at row 2, the code should read

row_number = ListBox1.ListIndex + 2

 

[MarkusLAndersson]

Thanks for you’re support problem solved! =)