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

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

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

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

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

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

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

I tried your code but the top line has an error in it. Do you know wha
that might be

--
mgmcdevit
-----------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread.php?threadid=46694

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

I had a "methog or data member not found"

In my list an example of a name is "CO Wage". My corresponding macro i
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

--
mgmcdevit
-----------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread.php?threadid=46694

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

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

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

I used a combination of both of your codes and it works well. Thanks fo
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 Su

--
mgmcdevit
-----------------------------------------------------------------------
mgmcdevitt's Profile: http://www.excelforum.com/member.php...fo&userid=2659
View this thread: http://www.excelforum.com/showthread.php?threadid=46694