How to activate a macro from combo box / list box

[furiousfox]

Hi everyone
I'm trying to figure out how to activate a macro from combo box or list box.
But no success.

I have a list of names:
AAA
BBB
CCC
111
222
333

That I can view through the combo box.


I have created a list of macros, that carry the same names,

AAA
BBB
CCC
111
222
333

The Q is: how can I link each name to its own macro?
so when chosen, will activate the macro?

Thanks for the help

[MickG]

Hi Try this if its a "Ciontrol Toolbox" Combobox.

Please Login or Register  to view this content.

How do you call a "Macro" (111) ?? I did'nt think you could do that!!
Regards Mick

[shg]

Or maybe

Please Login or Register  to view this content.

[MickG]

Hi, Sometimes you can't see the wood for the trees !!!
Mick

[furiousfox]

Hi Mick
Thanks for that, but do have another Q (I'm not an expert..)

Where exactly do I put the macro code itself?
I mean, should it be somthing like that:

Select Case ComboBox1.Value
Case "AAA"
Call AAA

Please Login or Register  to view this content.

End Select



Many Thanks!!

P.S.
The numbers was just for the example ;-)

[MickG]

Hi, As "shg" so knowledgeably pointed out, you don't need all that code if you just need to run a "Macro" from a Combobox when the Macro name is the same as the Combobox Box value.
If you combobox came from the "View" , "Control Toolbar" then do the following:-
Get the "Control Toolbar" menu on the sheet from "View"--.
Click the Green triangle icon, this gets the Combobox in Design Mode,
When you hold the cursor over the "ComboBox" the Cursor changes to Arrow Headed Cross.
Right Click Select Properties, Find "ListFillRange" enter the Range with you Macro names in i.e.. A1:A5
Double Click the Combobox, or right click and select "View Code"
Paste the code into the window, between "Private Sub ComboBox1_Change() " and "End Sub". to end up as shown below:-.

Please Login or Register  to view this content.

Click the Green Triangle on the VB Window "Toolbar"
This will turn the blue square to it's left Dark Blue. You are now in run mode.
Close VB Window.
Now when you select a value in your ComboBox the related "Macro" will run. (Hopefully)
---------
If you have a Combobox from the from the "forms" Menu then do the following:-
Right click, choose "Format Control", "Control", enter range as above in "Input Range"
Close "Format control"
Right click, Combobox (DropDown Box), choose, "Assign Macro".
Select you "Drop down Box" from the Menu, if New choose "New" else choose "Edit".
Paste the following into the VB window after the words :- "Sub DropDown?_Change()"
and before "End Sub"

Please Login or Register  to view this content.

Close VBWindow.
On selection of ComboBox value the chosen "Macro will run.
Hope that's everything
Regards Mick

[furiousfox]

Hi Mick
Thanks for your detailed post.

I followed your instructions (the 2'nd option)
The code I've added:

Please Login or Register  to view this content.

Then, following comes the macro code.

But when I activate the macro from the combo box,
I get this error message:

Run-time error '1004':
Unable to get the DropDown property of the worksheet class

What does it means?
Can you advice?

Thx,
Amit

[MickG]

Hi,I've copied your code from your last post and it works OK.
Try Removing that bit of code, replace it with the bit below. It should give you a Msgbox with name of the Drop Down Box.
see what you get.

Please Login or Register  to view this content.

Mick

[davesexcel]

Here is an attachment using examples from both posters.

[furiousfox]

Hi Guys
Thanks so much for your help,
You saved me a lot of time!!

Cheers
A.C.