Command Button Drop Downs

[Alan P]

I have a number of command buttons that I want to combine into one drop down
button. Each item on the list will use a different macro. I can't seem to
make this work, can someone point me in the right direction?

 

[Otto Moehrbach]

Alan
Assuming your Data Validation (drop-down) cell is A1, the following
macro shows you how to run a different macro for each item selected in A1.
Note that this macro must be placed in the sheet module of your sheet. The
called macros can be placed in a regular module. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A1" Then
Select Case Target.Value
Case "FirstItem": Call ThisMacro
Case "SecondItem": Call ThatMacro
Case "ThirdItem": Call TheOtherMacro
End Select
End If
End Sub

 

[Alan P]

Otto,

Thanks for the help. Unfortunately I am unable to get it to work. It keeps
looking for a Worksheet_Click() rather than Worksheet_Change(ByVal Target As
Range). If I enter Worksheet_Click(ByVal Target As Range) it gives me an
error. What obvious thing am I doing wrong?

Alan

 

[Otto Moehrbach]

What are you clicking on that is driving Excel to look for a Worksheet_Click
macro? Do you have a button?
I assumed that you had a Data Validation in some cell (A1 in my macro), that
you were clicking on the little down-arrow to the right of that cell, that
this produced a drop-down list, that you select one of the items in that
list by clicking on it. This will fire the Worksheet_Change macro that will
select the macro that goes with the item selected and will run that macro.
Apparently you don't have that. Tell me, in detail, what you have, what you
do, what you want to happen when you do that, and what is happening. Otto

 

[Alan P]

Hi Otto,

I am probably going in several directions at once. My original concept was
to have a combo box/drop down driving the macros. Based on your input I
tried a button with the normal drop down in cell A3. That's when I ran into
the Click vs. Change problem. After reading your response I eliminated the
button, went back to the worksheet change code and tried to run it by
changing the A3 drop down but nothing happened when I switched months in the
list. Here's the code, it's in the worksheet module, the macros are actually
in both sheet and module areas:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "A3" Then
Select Case Target.Value
Case "October": Call Oct
Case "November": Call Nov
Case "December": Call Dec
Case "January": Call Jan
Case "February": Call Feb
Case "March": Call Mar
Case "April": Call Apr
Case "May": Call May
Case "June": Call Jun
Case "July": Call Jul
Case "August": Call Aug
Case "September": Call Sep

End Select
End If
End Sub

I thought this was the correct code but nothing happens when I change the
input months in A3.

Thanks for your help.

Alan

 

[Dave Peterson]

If Target.Address = "A3" Then
should be:
If Target.Address = "$A$3" Then
or
If Target.Address(0,0) = "A3" Then

 

[Otto Moehrbach]

Alan
Forgive me, I made a mistake in the code I sent you. The statement:
If Target.Address = "A3" Then
should be:
If Target.Address(0, 0) = "A3" Then
The complete code is below. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A3" Then
Select Case Target.Value
Case "October": Call Oct
Case "November": Call Nov
Case "December": Call Dec
Case "January": Call Jan
Case "February": Call Feb
Case "March": Call Mar
Case "April": Call Apr
Case "May": Call May
Case "June": Call Jun
Case "July": Call Jul
Case "August": Call Aug
Case "September": Call Sep
End Select
End If
End Sub

 

[Alan P]

Otto,

That worked great. I'm sure I'll use that for other worksheets in the
future. Thanks for your help.

Regards,

Alan

 

[Alan P]

Dave,

Thanks for your help as well. between you and Otto I have a solution that
works.

Regards,
Alan

 

[Dana DeLouis]

Select Case Target.Value

Case "October": Call Oct
Case "November": Call Nov
Case "December": Call Dec

Just throwing out an idea:

Sub Demo()
Dim S1 As String
Dim S2 As String
S1 = Target.Value
S2 = Format(DateValue(S1 & " 1,2008"), "mmmm")
If StrComp(S1, S2, vbTextCompare) = 0 Then
'It's a valid month name
Run Format(DateValue(S1 & " 1,2008"), "mmm")
End If
End Sub

 

[Otto Moehrbach]

Thanks Dana. It's always good to see another way of doing it. I'll keep
this in mind. Otto

 

[Dana DeLouis]

Probably not useful, but here's another general idea:

Sub Demo()
Dim StrMonth As String
Dim N As Long

StrMonth = "February"
On Error Resume Next
With Application
N = .Match(StrMonth, .GetCustomListContents(4), 0)
If N > 0 Then Run .GetCustomListContents(3)(N)
End With
End Sub

Sub Feb()
MsgBox "Doing Feb"
End Sub