Can I assign a macro to each item in a drop-down list?

[jbp20717]

Is it possible to assign a different macro to each item in a drop down
list or not?

I've tried something like this, but I am totally useless with
VisualBasic, so I wouldn't be surprised if its totally meaningless:

Sub Larder2()
'
Select Case ("Larder Prep")
Sheets("Larder Prep").Select
Select Case ("Bakery")
Sheets("Bakery").Select
End Sub

...besides, I want to run a range of macros and not select sheets in
the workbook.

 

[Bob Phillips]

No, but you could have the macro that fires when you select (I assume it is
a Forms listbox?) to test the value and fire the appropriate macro.

 

[jbp20717]

How would I go about that? ;

 

[Rich_z]

If you go into your form and click the list box you will see some cod
appear like this:


Code
-------------------

Private Sub Cust_Details_Change()

End Sub

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


Place a case statement in here for each item in your list box:


Code
-------------------

Private Sub Larder_Prep_Change()
Select Case Larder_Prep.Text
Case "Bakery"
Call Routine to do bakery
Case "Fry-Ups"
Call Yumm
Case Else
'*
'* .... Mans Work
'*
Call Do_Washing_Up
End Select
End Sub

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


You then put the routines to do whatever in as sub routines betwee
Sub.. End Sub.... commands.

Check out pressing the F1 key while in the VB Editor - it tells you
lot!

Regards

Ric

 

[Bob Phillips]

Assign the macro to the listbox and add this code to that macro

Sheets(ActiveSheet.ListBoxes(Application.Caller).Value).Select


But the sheet tabs at the bottom also have a list, just right-click the
arrow keys to the left of the tab names.

 

[Guest]

This code should be assigned to the FORMS combo box, where cell D9 is the
linked cell. Each time you select a value, D9 gets that value and the code
runs.

Sub RunFromCellValue()
Application.Run Range("D9").Value
End Sub
the catch is that value must correspond to the code to be run

Alternative is to use an index
Sub RunFromCellValue()
select case range("D10") ' where D10 is the position of the selected item
case 1: Call MyProc_1
case 2: Call MyProc_2
case 3: Call Something else
Case Else
End Select
End Sub

If you're using the ActiveX combo box, then simply use the control's
listindex and use the select case method. Note , if the index is -1 then
nothing was selected.