Using "case" code in Excel

[Howard]

I have the following problem. Cell J2 contains a drop-down menu of week
days. I am trying to use VBA to check the text in cell J2, and then run a
macro that matches the week day. The code follows, but it doesn't seem to
work. Any suggestions?

Sub selectthecase()

Dim r As Range
Set r = Range("J2")

Select Case r
Case Is = "MONDAY"
Call MondayMacro
Case Is = "TUESDAY"
Call TuesdayMacro
Case Is = "WEDNESDAY"
Call WednesdayMacro
Case Is = "THURSDAY"
Call ThursdayMacro
Case Is = "FRIDAY"
Call FridayMacro
Case Is = "SATURDAY"
Call SaturdayMacro
End Select

End Sub

 

[Dave Peterson]

Do you really have MONDAY in J2? All upper case???

If it's not all upper case, ...

select case lcase(r.value)
case is = lcase("monday")
call MondayMacro
case is = lcase("tuesday")
....

 

[Howard]

Dave:
Yes, Monday is all uppercase. I use a drop-down menu so that the user has
to enter the correct spelling. When I manually run the sub, it works, but I
think that what I need is script that makes the sub run when ever the text in
J2 is changed. Any ideas?
Thanks

 

[JLGWhiz]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("J2") Then
Dim r As Range
Set r = Range("J2")

Select Case r
Case Is = "MONDAY"
Call MondayMacro
Case Is = "TUESDAY"
Call TuesdayMacro
Case Is = "WEDNESDAY"
Call WednesdayMacro
Case Is = "THURSDAY"
Call ThursdayMacro
Case Is = "FRIDAY"
Call FridayMacro
Case Is = "SATURDAY"
Call SaturdayMacro
End Select

End If
End Sub

Dave:
Yes, Monday is all uppercase. I use a drop-down menu so that the user has
to enter the correct spelling. When I manually run the sub, it works, but I
think that what I need is script that makes the sub run when ever the text in
J2 is changed. Any ideas?
Thanks

 

[Howard]

Not sure what I'm doing wrong. It still doesn't work. Maybe I'm explaining
it wrong. When I go to the worksheet and change cell J2 from one day (say
Monday) to another, (say Tuesday), I want the sub to run and run the macro
associated with either Monday or Tuesday, etc.
PS--I really appreciate the help from you people. Thanks

 

[JLGWhiz]

You need to put the code I gave you in the worksheet code module. Right
click the sheet tab for the sheet you have the dropdown box on and select
"View Code". When the VBE window opens, paste the code into that window.
Then try it. If it works, save the file.

Worksheet_change event code has to be in the code module for the sheet it
applies to. It will not work from the standard Module1.

 

[Howard]

I tried that and I get an error message that says:
Compile error:
Ambiguous name detected: Worksheet_change

 

[JLGWhiz]

You have to delete the old code in the stadard module1.

You can't have a code in the standard module with the same name. It
confuses the compiler.

You also cannot have two worksheet_change event codes in the same sheet
module.

You can, however, combine two event procedures under one change event title.

I am going to bed now. If you have more proplems, start a new thread and
maybe someone else will pick it up.

 

[Howard]

Thanks for all your help, and have a good eveing.

 

[Dana DeLouis]

Just a slightly different idea...
If there really is no Sunday, then just have a Sunday Macro that does
nothing.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> Range("J2").Address Then Exit Sub

Dim WD As String 'WeekDay
Dim Valid As Boolean

WD = Target.Value
On Error Resume Next
Valid = WorksheetFunction.Match(WD, _
Application.GetCustomListContents(2), 0) > 0
If Valid Then Run WD & "Macro"
End Sub

= = =
HTH
Dana DeLouis