Combo Box Example

Posted by Artem on March 21, 2001 2:21 PM

Guys PLEASE help! I'm new to VBA - all I'm trying to do is a simple ComboBox on a
worksheet (not on a User Form). I succeded only in draging the control onto a
sheet and sizing it - the rest is VBA. I want the Combo Box to be populated using
VBA (not data range from a sheet) and when a certain entry is selected I want it
to run a macro (e.g changing color of cell "A1"). Is there a simple example
(tutorial, website) which explains which code to put and in which module?
THANKS!!!

also posted on microsoft.public.excel.programming

Posted by Roger Redhat on March 22, 2001 4:01 AM

If you right click on the sheet tab of your worksheet, select View Code and then paste this code into the sheet module you should get some idea of how it's all working:-

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Dogs"
MsgBox "Dogs"
Case "Cats"
MsgBox "Cat"
Case "Mice"
MsgBox "Mice"
Case Else
End Select
End Sub

Private Sub Worksheet_Activate()
ComboBox1.Clear
ComboBox1.AddItem "Dogs"
ComboBox1.AddItem "Cats"
ComboBox1.AddItem "Mice"
ComboBox1.Text = ComboBox1.List(0)
End Sub

The combobox is populated whenever the sheet is activated (although you could also populate just when the workbook opens). Whenever the entry is changed within the combobox the Change event if fired and you can then run different code depending on what the user has chosen.

Hope this helps,
Roger.

Posted by Artem on March 23, 2001 1:23 PM

THANK YOU VERY MUCH! That exactly what i was looking for.