Archive of Mr Excel Message Board


Back to Controls in Excel archive index
Back to archive home

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


Re: Combo Box Example

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.


Re: Combo Box Example

Posted by Artem on March 23, 2001 1:23 PM
THANK YOU VERY MUCH! That exactly what i was looking for.



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.