Forum Discussion

Rich Foster's avatar
Rich Foster
Copper Contributor
Jun 25, 2018

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 ControlSource in the properties for the ListBox but that option does not exist on the Mac version.

I worked around the missing RowSource choice in properties by manually adding my list items into the ListBox but I can not figure out this next step!

Macros & VBA
Requests

1 Reply

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 28, 2018

    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

     

    https://techcommunity.microsoft.com/t5/Macros-and-VBA/Trying-to-create-a-GUI-that-can-save-input-values-and-output/m-p/210148#M1054

Resources