Populate Combobox Values at a click of button

I want to populate the Combobox with the list of predefined values declared in another sheet in same excel at a click of command button



srikanthv2322Asked:
Who is Participating?
 
SiddharthRoutCommented:
Please amend as applicable.

Private Sub CommandButton1_Click()
    '~~> This will populate from Row 1 to Row 10
    '~~> from Col A in Sheet 2
    For i = 1 To 10
        ComboBox1.AddItem Sheets("Sheet2").Range("A" & i).Value
    Next
End Sub

Open in new window


Sid
0
 
Zack BarresseCEOCommented:
I would amend Sid's code to include a clear functionality, which will not make your list "double" when you click the button a second time...
Private Sub CommandButton1_Click()
    Dim i As Long
    ComboBox1.Clear
    For i = 1 To 10
        ComboBox1.AddItem Sheets("Sheet2").Range("A" & i).Value
    Next
End Sub

Open in new window

Or, you can use another method, one I prefer, to enter the data all at once instead of looping (I hate looping)...
Private Sub CommandButton1_Click()
    ComboBox1.Clear
    ComboBox1.List = Sheets("Sheet2").Range("A1:A10").Value
End Sub

Open in new window

HTH
0
 
SiddharthRoutCommented:
Thanks Zack

I missed on "ComboBox1.Clear". Nice catch!

Yeah, I completely agree with you on "Looping".

Sid
0
 
Zack BarresseCEOCommented:
Just another way to skin the cat.  I'm not really sure what the OP's end-game is with this particular query, which is why I posted the secondary routine.  It may in fact be a loop is what is called for - I have no idea! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.