Running a Macro depending on Validation List Selection

F

Fester

Is there a way to run a Macro depending on the result of a validation
list selection?

I have a list of store names with specific number of techs.
I select Shop A and Macro 1 Runs
I select Shop B and Macro 2 Runs, etc.

What I want to accomplish is depending on the value in a cell, it will
run the specific Macro.

Any help is appreciated and Thank you in advance for the help.

Fester
 
G

Gord Dibben

Fester

Assuming A1 has the DV drop-down.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Select Case LCase(Target.Value)
Case "value1"
Macro1
Case "value2"
Macro2
Case "value3"
Macro3
End Select
End If
enditall:
Application.EnableEvents = True
End Sub

Replace "value1", "value2" etc. with your list items.

This is worksheet event code.

Right-click on the sheet tab and "View Code". Paste into that module.


Gord Dibben Excel MVP
 
B

Bob Phillips

If you have XL97, you will need to use the Calculate event, and link a cell
to your DV cell to force a calculate.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Debra Dalgleish

Or use a delimited list.

Bob said:
If you have XL97, you will need to use the Calculate event, and link a cell
to your DV cell to force a calculate.
Click to expand...
 
F

Fester

I'm sorry, I want to select a command button, not a macro, is the
process still the same? Instead of the Macro, could I just paste the
code from the button click under the case option?
 
Top