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!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=552879

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
--
AP

"m3s3lf" a écrit dans
le message de news: ...

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!


--
m3s3lf
------------------------------------------------------------------------
m3s3lf's Profile:
http://www.excelforum.com/member.php...o&userid=34874
View this thread: http://www.excelforum.com/showthread...hreadid=552879

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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=552879

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

Leith Ross Wrote:
Hello m3s3lf,

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

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

Link this Macro to the Drop Down. When the user makes a selection, th
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

-------------------


Sincerely,
Leith Ros

--
m3s3l
-----------------------------------------------------------------------
m3s3lf's Profile: http://www.excelforum.com/member.php...fo&userid=3487
View this thread: http://www.excelforum.com/showthread.php?threadid=55287