How can I assign a macro to each name in a drop down list

[mgmcdevitt]

I have macros for each name already written. But I want to select a name from a drop down list and have the designated macro run. And if I change the name I want that designated macro to run.

Please explain how I can do this in as much detail as possible. I am not very familar with macros.

Thanks for your help

[Jim Thomlinson]

Create a validation list in Cell A1 (you can change this address but you will
also have to change the code where it references "$A$1") by selecting Cell A1
-> Data -> Validation. Now change any value to List and add your names to the
list area provided (separated by commas or reference a range of cells with
the name listed). Right click on the sheet tab and select view code. The VB
editor will open up. Paste the following code into the code window... That
should be it. You will need to modify the names and the call procedures to
match you list and your macros.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Select Case Target.Value
Case "Dave"
Call Module1.DaveProcedure
Case "Bob"
Call Module1.BobProcedure
Case "Tom"
Call Module1.TomProcedure
Case Else
MsgBox Target.Value & " Caused an error"
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"mgmcdevitt" wrote:

>
> I have macros for each name already written. But I want to select a name
> from a drop down list and have the designated macro run. And if I change
> the name I want that designated macro to run.
>
> Please explain how I can do this in as much detail as possible. I am
> not very familar with macros.
>
> Thanks for your help
>
>
> --
> mgmcdevitt
> ------------------------------------------------------------------------
> mgmcdevitt's Profile: http://www.excelforum.com/member.php...o&userid=26592
> View this thread: http://www.excelforum.com/showthread...hreadid=466940
>
>

[Gord Dibben]

Assuming the drop-down list is one created using Data Validation.

Copy/paste this code into the sheet module by right-click on sheet tab and
"view code".

Paste into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
If Target.Address = "$E$1" Then
Select Case LCase(Target.Value)
Case "bob"
bob 'run bob macro
Case "donna"
donna 'run donna macro
Case "gord"
gord 'run gord macro
Case "pete"
pete 'run pete macro
End Select
End If
CleanUp:
Application.EnableEvents = True
End Sub

Adjust the target address to suit and add cases to suit also.


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 17:01:14 -0500, mgmcdevitt
<[email protected]> wrote:

>
>I have macros for each name already written. But I want to select a name
>from a drop down list and have the designated macro run. And if I change
>the name I want that designated macro to run.
>
>Please explain how I can do this in as much detail as possible. I am
>not very familar with macros.
>
>Thanks for your help

[mgmcdevitt]

I had a "methog or data member not found"

In my list an example of a name is "CO Wage". My corresponding macro is named COWage. How would the code look if I used these.

I put in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$9" Then
Select Case Target.Value
Case "CO Wage"
Call Module1.COWageProcedure
Case Else
MsgBox Target.Value & " Caused an error"
End Select
End If
End Sub

Isn't this correct?

[Rowan]

If your macro is called COWage then change the line:

Call Module1.COWageProcedure
to
Call Module1.COWage

Regards
Rowan

"mgmcdevitt" wrote:

>
> I had a "methog or data member not found"
>
> In my list an example of a name is "CO Wage". My corresponding macro is
> named COWage. How would the code look if I used these.
>
> I put in:
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$G$9" Then
> Select Case Target.Value
> Case "CO Wage"
> Call Module1.COWageProcedure
> Case Else
> MsgBox Target.Value & " Caused an error"
> End Select
> End If
> End Sub
>
> Isn't this correct?
>
>
> --
> mgmcdevitt
> ------------------------------------------------------------------------
> mgmcdevitt's Profile: http://www.excelforum.com/member.php...o&userid=26592
> View this thread: http://www.excelforum.com/showthread...hreadid=466940
>
>

[mgmcdevitt]

I used a combination of both of your codes and it works well. Thanks for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$9" Then
Select Case Target.Value
Case "CO Wage"
COWage 'run COWage macro
Case "AZ Wage"
AZWage 'run AZWage macro
Case Else
MsgBox Target.Value & " Caused an error"
End Select
End If
End Sub