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: 2011-03-05

Who is Participating?

LVL 30

SiddharthRoutCommented: 2011-03-05

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

LVL 14

Zack BarresseCEOCommented: 2011-03-05

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

LVL 30

SiddharthRoutCommented: 2011-03-05

Thanks Zack

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

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

Sid

LVL 14

Zack BarresseCEOCommented: 2011-03-05

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! :)