ListBox Selection to fire Macro

[Kaye]

Hi - Is it possible to have a dropdown box (listbox/combobox?) of 13
text choices, with each choice assigned to a macro?

Ideally I'd like to run one of 13 macros depending upon what the user
selects from this dropdown box.

My present method is using 13 checkboxes with a different macro
assigned to each - the user ticks it, and my macro fires. This though
is occupies too much screen area.

Any help would be appreciated.

Regards Kaye

 

[Doug Glancy]

Kaye,

You can use data validation in the cell, with a list of your 13 choices.
Then in that sheet's code module insert a Worksheet_Change Event module.
Assuming your data validated cell is in A1 and your choices are "a", "b" and
"c" you could use code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1").Value
Case "a"
Call MacroA
Case "b"
Call MacroB
Case "c"
Call MacroC
End Select
End If
End Sub

hth,

Doug

 

[Kaye]

Thanks for that Doug, that's what I was after!

But - another small problem has come to light ....

I have TWO cells that carry Data Validation list boxes - A1, and also
at A2, just beneath it.

It appears I can't have TWO "Worksheet_Change" macros on the same
sheet, even though my "Target, Range" is a different cell, that being
A2 rather than A1.

Is there any way I can have TWO "Worksheet_Change" macros on the same
sheet?

Kaye

 

[Doug Glancy]

Kaye,

You can't have two Worksheet_Change events, but in your single event you can
test for each cell separately. Duplicate the code for A2 so that it looks
something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1").Value
Case "a"
Call MacroA
Case "b"
Call MacroB
Case "c"
Call MacroC
End Select
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Range("A2").Value
Case "1"
Call Macro1
Case "2"
Call Macro2
Case "3"
Call Macro3
End Select
End If
End Sub

The above assumes that the validation for A2 is different. But if it's the
same, just modify it back to the "A" "B" and "C" values.

hth,

Doug

 

[Kaye]

Thanks Doug, but I'm having a problem.

Yes, you are correct in your assumption that the validation for A2 is
different.

When I run this new code, from A1 or A2, this is returned:
"Compile Error: Block IF without ENDIF"

The last line of the code, END SUB, is highlighted blue, and
the first line, "Private sub Worksheet_Change etc is highlighted
yellow.

I've transposed your code correctly. Any clues?

Regards, Kaye

 

[Doug Glancy]

Kaye,

Woops. Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Range("A1").Value
Case "a"
Call MacroA
Case "b"
Call MacroB
Case "c"
Call MacroC
End Select
End If
If Not Intersect(Target, Range("A2")) Is Nothing Then
Select Case Range("A2").Value
Case "1"
Call Macro1
Case "2"
Call Macro2
Case "3"
Call Macro3
End Select
End If
End Sub

hth,

Doug

 

[Kaye]

Thanks so much Doug, it's working great now!

Kaye

 

[Guest]

Hi
I was looking for help with macros and assigning them to data validation and
saw your posting reply.

What im trying to do is simialr but i have 3 choices in the drop down
validation,
CHOICE A (macro 1)
CHOICE B (macro 2)
CHOICE C (macro 3)

but i need to do this to 9 seperate towns. These are on seperate worksheets
marked as below. the macro that drives the print is the same one but as you
change the worksheet it selects that sheets info.

TOWN 1
TOWN 2
TOWN 3 ETC

I basically select the choice from the data validation dropdown and I want
it to then print using the macro i've set up on the relevant town worksheet

Is this possible to do.

Any help you can offer would be appreciated!