| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lower case letter "i" always converts to upper case | Excel Discussion (Misc queries) | |||
| Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| how do I count only lower case "x" and exclude upper case "X" | Excel Worksheet Functions | |||
| Fix Code: Select Case and "Contains" selection | Excel Programming | |||
Hybrid Mode
