View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Leith Ross[_592_] Leith Ross[_592_] is offline
external usenet poster
 
Posts: 1
Default Linking a Drop Down Cell with the Macro


Hello white-ryland,

Here is a macro I wrote for a similar situation to yours. You may find
this helpful.

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. Expand the number of Case statements to match the number of
lines in the DropDown. 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.

'Start 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

'End Code


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=554782