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

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.

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.

--
HTH

Bob Phillips

"jbp20717" <jbp20717.1rn0yc_1120467910.2963@excelforum-nospam.com> wrote in
message news:jbp20717.1rn0yc_1120467910.2963@excelforum-nospam.com...
>
> 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.
>
>
> --
> jbp20717
> ------------------------------------------------------------------------
> jbp20717's Profile:
http://www.excelforum.com/member.php...o&userid=24886
> View this thread: http://www.excelforum.com/showthread...hreadid=384257
>

How would I go about that?

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

Private Sub Cust_Details_Change()

End Sub

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

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 between Sub.. End Sub.... commands.

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

Regards

Rich

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.

--
HTH

Bob Phillips

"jbp20717" <jbp20717.1rn6ic_1120475103.7734@excelforum-nospam.com> wrote in
message news:jbp20717.1rn6ic_1120475103.7734@excelforum-nospam.com...
>
> How would I go about that?
>
>
> --
> jbp20717
> ------------------------------------------------------------------------
> jbp20717's Profile:
http://www.excelforum.com/member.php...o&userid=24886
> View this thread: http://www.excelforum.com/showthread...hreadid=384257
>

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.


"jbp20717" wrote:

>
> 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.
>
>
> --
> jbp20717
> ------------------------------------------------------------------------
> jbp20717's Profile: http://www.excelforum.com/member.php...o&userid=24886
> View this thread: http://www.excelforum.com/showthread...hreadid=384257
>
>