Macro based on cell value
 

I have a question similar to one that has been posted before but I tried all
those answers and couldn't get it to work. I have...let's say...12 different
macros, one for each month of the year, that I can assign to buttons and they
work fine. Jan07, Feb07, and so on. On sheet2 in my workbook, Cells F9 and
G9 are merged together and contain a data validation drop down list of those
months. What I would like is that when January-07 is selected, it will run
Jan07, when February-07 is selected it will run Feb07, and so on. One thing
I thought may be affecting it is the fact that when I select a month from the
drop down list, I see it as "January-07" but it seems as though excel has it
as 1/1/2007. Meaning, if I double click the cell to modify it, it would
change from "January-07" to "1/1/2007".

Any help would be greatly appreciated.

Thanks,
Scott

Macro based on cell value
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Application.Run Format(.Value, "mmmyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Scott Marcus" wrote in message
...
I have a question similar to one that has been posted before but I tried
all
those answers and couldn't get it to work. I have...let's say...12
different
macros, one for each month of the year, that I can assign to buttons and
they
work fine. Jan07, Feb07, and so on. On sheet2 in my workbook, Cells F9
and
G9 are merged together and contain a data validation drop down list of
those
months. What I would like is that when January-07 is selected, it will
run
Jan07, when February-07 is selected it will run Feb07, and so on. One
thing
I thought may be affecting it is the fact that when I select a month from
the
drop down list, I see it as "January-07" but it seems as though excel has
it
as 1/1/2007. Meaning, if I double click the cell to modify it, it would
change from "January-07" to "1/1/2007".

Any help would be greatly appreciated.

Thanks,
Scott

Macro based on cell value
 

Thank you Bob, but I'm not exactly sure what this macro is accomplishing. My
objective is that when cell F9:G9 (they're merged) reads "January-07" this
event macro would call another macro named "Jan07". If the cell reads
"February- 07" it would call macro "Feb07", and so on. I don't know how to
include this into the macro.

Thanks again,
Scott

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Application.Run Format(.Value, "mmmyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Scott Marcus" wrote in message
...
I have a question similar to one that has been posted before but I tried
all
those answers and couldn't get it to work. I have...let's say...12
different
macros, one for each month of the year, that I can assign to buttons and
they
work fine. Jan07, Feb07, and so on. On sheet2 in my workbook, Cells F9
and
G9 are merged together and contain a data validation drop down list of
those
months. What I would like is that when January-07 is selected, it will
run
Jan07, when February-07 is selected it will run Feb07, and so on. One
thing
I thought may be affecting it is the fact that when I select a month from
the
drop down list, I see it as "January-07" but it seems as though excel has
it
as 1/1/2007. Meaning, if I double click the cell to modify it, it would
change from "January-07" to "1/1/2007".

Any help would be greatly appreciated.

Thanks,
Scott

Macro based on cell value
 

The macro will trigger when anything is selected from the list, and fires
the corresponding macro.

Change the target range and it should work.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Scott Marcus" wrote in message
...
Thank you Bob, but I'm not exactly sure what this macro is accomplishing.
My
objective is that when cell F9:G9 (they're merged) reads "January-07" this
event macro would call another macro named "Jan07". If the cell reads
"February- 07" it would call macro "Feb07", and so on. I don't know how
to
include this into the macro.

Thanks again,
Scott

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Application.Run Format(.Value, "mmmyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Scott Marcus" wrote in message
...
I have a question similar to one that has been posted before but I
tried
all
those answers and couldn't get it to work. I have...let's say...12
different
macros, one for each month of the year, that I can assign to buttons
and
they
work fine. Jan07, Feb07, and so on. On sheet2 in my workbook, Cells
F9
and
G9 are merged together and contain a data validation drop down list of
those
months. What I would like is that when January-07 is selected, it
will
run
Jan07, when February-07 is selected it will run Feb07, and so on. One
thing
I thought may be affecting it is the fact that when I select a month
from
the
drop down list, I see it as "January-07" but it seems as though excel
has
it
as 1/1/2007. Meaning, if I double click the cell to modify it, it
would
change from "January-07" to "1/1/2007".

Any help would be greatly appreciated.

Thanks,
Scott