assign a macro to a combo box

[Guest]

Hi

I'm a fairly new user to Macros

I have a worksheet created in XL2003 that contains five buttons created from
the toolbox. Each button successfully runs its own unique macro basically
hiding and showing specific rows. The Macros are caller Sub Macro1( ) to Sub
Macro5( )

Having just come accross Combo boxes where the source data is allocated a
numerical value ie "One"=1, "Two"=2 etc, I would like to allocate Sub Macro1(
) to "One" and Sub Macro2( ) to "Two" etc. so that I can save a lot of space
in my workbook

How can I do this please

Many thanks

 

[Guest]

You can link a cell to your combo box. That cell will then contain the index
of the item selected. Assuming you link to Cell A1 this code should work for
you...

Sub ComboCode()
Select Case Range("A1").Value
Case 1
Call Macro1
Case 2
Call Macro2
Case 3
Call Macro3
Case 4
Call Macro4
Case 5
Call Macro5
End Select
End Sub

 

[Guest]

Hi Jim

Thanks for the reply

This takes me part way to where I would like to get to and is workable for
me. I can just create a Run macro button.

However, my ideal solution would be that in having selected 3 from the combo
box drop down, the selection of that choice would run macro3.

Is this possible or am I asking for too much

Thanks

Dave