Running a macro based on combo box value

[m3s3lf]

I've searched (and searched and searched), but I can't get the darn
thing to work. I've got a combo box (which I've inserted using the
Form toolbar) and I've given it options and values for said options.
I've written 9 macros that I'd like to have run depending on which
option is selected from the combo box. But I can't get the main macro
(monitoring the combo box and firing any of the 9 macros depending on
it's value) to identify the combo box. Does anybody have some insight?
I'm just about ready to send it to somebody and pay them to fix it...
any takers there?
Thanks in advance!

 

[Ardus Petus]

Forms Toolbar Comboboxes are not ActiveX objects. You cannor directly access
their properties using VBA.

Within the associated OnChange macro, you can read the value of the linked
cell.

I think you'd better use a Controls Toolbar Combo.

HTH

 

[Leith Ross]

Hello m3s3lf,

If I understand you correctly, this macro should work. This example
uses a Forms Drop Down with 3 lines for brevity. You can expand this
example to run as many macros as you have entries.

First copy this code using CTRL+C then insert a VBA Module into your
Workbook's Project. Paste the code into the new Module. Add the names
of the macros to be called into the Select Case code below each Case
statement. Be sure to save your changes using CTRL+S.

Link this Macro to the Drop Down. When the user makes a selection, the
macro will determine which line number was selected in the Drop Down.
This line number then determines which macro will be executed.


Code:
--------------------
Sub RunMacrosFromDropDown()

'Get the Name of ComboBox (Drop Down)
Cbo = Application.Caller

'Get the line number of the entry and the entry data
With ActiveSheet.Shapes(Cbo).ControlFormat
CboLine = .ListIndex
CboData = .List(CboLine)
End With

'Select Macro to run based on the Drop Down line selected
Select Case CboLine
Case 1
'Call First Macro
Case 2
'Call Second Macro
Case 3
'Call Third Macro
End Select

End Sub

 

[m3s3lf]

It's working!! I'm so happy! Thank you!!