VBA UserForms in Excel
VBA UserForms in Excel
(OP)
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
-----------------------------------------------------------
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
-----------------------------------------------------------





RE: VBA UserForms in Excel
Sheets("sheet1").Cells(8, 1).Value = ListBox1.Value
RE: VBA UserForms in Excel
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)
RE: VBA UserForms in Excel
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.
RE: VBA UserForms in Excel
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
RE: VBA UserForms in Excel