Forum Discussion
Linking user form listBox selection to a spreadsheet cell
I am using excel 2011 on a Mac. I have set up a user form with a ListBox of 10 numbers. How do I select an value from that list and place it into a spreadsheet cell? On a PC I would use the ControlSo...
You can loop through listbox values and see if they are selected like this:
Sub GetListBoxVals()
Dim i As Long
For i = 1 To ActiveSheet.ListBoxes("List Box 1").ListCount
If ActiveSheet.ListBoxes("List Box 1").Selected(i) Then
'If listbox item is selected then do some stuff here
Range("A1") = ActiveSheet.ListBoxes("List Box 1").List((i))
End If
Next i
End Sub
Sub GetListBoxValsUserForm()
Dim i As Long
For i = 1 To Me.ListBox1.ListCount
If Me.ListBox1.Selected(i) Then
'If listbox item is selected then do some stuff here
Range("A1") = Me.ListBox1.List(i)
End If
Next i
End Sub
If you just need to load the form with values you can use the code in this threads example file to do it (You will want to change intLp to the range where you're list is. ie.e Range("A" & intLp):
'The numbers in the loop will need to correspond to your range as well...
Me.cboRatNum.AddItem Range("A" & intLp)
Private Sub UserForm_Initialize()
Dim intLp As Integer
'Load cboRatNumber with values....
For intLp = 1 To 28
Me.cboRatNum.AddItem intLp
Next intLp
End Sub