|
Using "case" code in Excel
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 -- Howard |
Using "case" code in Excel
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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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 -- Howard "JLGWhiz" wrote: 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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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" wrote: 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 -- Howard "JLGWhiz" wrote: 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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
I tried that and I get an error message that says:
Compile error: Ambiguous name detected: Worksheet_change -- Howard "JLGWhiz" wrote: 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" wrote: 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 -- Howard "JLGWhiz" wrote: 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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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" wrote: I tried that and I get an error message that says: Compile error: Ambiguous name detected: Worksheet_change -- Howard "JLGWhiz" wrote: 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" wrote: 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 -- Howard "JLGWhiz" wrote: 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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
Thanks for all your help, and have a good eveing.
-- Howard "JLGWhiz" wrote: 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" wrote: I tried that and I get an error message that says: Compile error: Ambiguous name detected: Worksheet_change -- Howard "JLGWhiz" wrote: 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" wrote: 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 -- Howard "JLGWhiz" wrote: 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 "Howard" wrote: 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 "Dave Peterson" wrote: 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 wrote: 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 -- Howard -- Dave Peterson |
Using "case" code in Excel
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 Howard wrote: Thanks for all your help, and have a good eveing. |
| All times are GMT +1. The time now is 01:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com