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?

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" <(E-Mail Removed)> wrote in message
news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
>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,

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
> >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?
>
>
>

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" <(E-Mail Removed)> wrote in message
news:0A026845-ED12-4160-8647-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
>> >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?
>>
>>
>>

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


"Otto Moehrbach" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:0A026845-ED12-4160-8647-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
> >> >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?
> >>
> >>
> >>
>
>
>

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


Alan P wrote:
>
> 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
>
> "Otto Moehrbach" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:0A026845-ED12-4160-8647-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > >> 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" <(E-Mail Removed)> wrote in message
> > >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
> > >> >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?
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

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" <(E-Mail Removed)> wrote in message
news:F096F645-EEE8-4920-904C-(E-Mail Removed)...
> 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
>
>
> "Otto Moehrbach" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:0A026845-ED12-4160-8647-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
>> >> >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,

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

Regards,

Alan

"Otto Moehrbach" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:F096F645-EEE8-4920-904C-(E-Mail Removed)...
> > 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
> >
> >
> > "Otto Moehrbach" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:0A026845-ED12-4160-8647-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in message
> >> >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
> >> >> >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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Dave,

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

Regards,
Alan

"Dave Peterson" wrote:

> If Target.Address = "A3" Then
> should be:
> If Target.Address = "$A$3" Then
> or
> If Target.Address(0,0) = "A3" Then
>
>
> Alan P wrote:
> >
> > 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
> >
> > "Otto Moehrbach" wrote:
> >
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:0A026845-ED12-4160-8647-(E-Mail Removed)...
> > > > 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" wrote:
> > > >
> > > >> 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" <(E-Mail Removed)> wrote in message
> > > >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
> > > >> >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?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
> --
>
> Dave Peterson
>

> 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

--
Dana DeLouis


"Otto Moehrbach" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:F096F645-EEE8-4920-904C-(E-Mail Removed)...
>> 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
>>
>>
>> "Otto Moehrbach" wrote:
>>
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:0A026845-ED12-4160-8647-(E-Mail Removed)...
>>> > 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" wrote:
>>> >
>>> >> 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" <(E-Mail Removed)> wrote in message
>>> >> news:CAA89EE6-F4DF-4CEE-8591-(E-Mail Removed)...
>>> >> >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?
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>