Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
I have created a Private Sub Worksheet_Change(ByVal Target As Range), with a
Select Case argument. When the value of cell D6 is equal to HOUSE, then the interior color of cell A8 becomes yellow. But if the user enters House in cell D6, then it doesnt work. How can I overcome case sensitivity? TIA JohnL |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
It is always a good idea to show the code you are using for the problem area
you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) "JohnL" wrote in message ... I have created a Private Sub Worksheet_Change(ByVal Target As Range), with a Select Case argument. When the value of cell D6 is equal to HOUSE, then the interior color of cell A8 becomes yellow. But if the user enters House in cell D6, then it doesnt work. How can I overcome case sensitivity? TIA JohnL |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
Rick, what it looks looks like is this:
Select Case Range("D6").Value Case "HOUSE" Range("A8).Interior.ColorIndex = 6 Range("A8").Font.ColorIndex = 2 "Rick Rothstein" wrote: It is always a good idea to show the code you are using for the problem area you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
Notice Rick's suggestion included ucase().
Select Case UCase(Target.Value) So you could use: Select Case ucase(Range("D6").Value) Another alternative if you don't care about any case comparisons... Add this line to the top of the module: Option Compare Text JohnL wrote: Rick, what it looks looks like is this: Select Case Range("D6").Value Case "HOUSE" Range("A8).Interior.ColorIndex = 6 Range("A8").Font.ColorIndex = 2 "Rick Rothstein" wrote: It is always a good idea to show the code you are using for the problem area you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
Thanks Dave. I was confused with (Target.Value) but your line was very
explanatory. My Worksheet Sub works like a charm now. Couldn't use your second suggestion because this isn't in a module. Thanks Rick and Dave for your time. I always learn things at this site. JohnL "Dave Peterson" wrote: Notice Rick's suggestion included ucase(). Select Case UCase(Target.Value) So you could use: Select Case ucase(Range("D6").Value) Another alternative if you don't care about any case comparisons... Add this line to the top of the module: Option Compare Text JohnL wrote: Rick, what it looks looks like is this: Select Case Range("D6").Value Case "HOUSE" Range("A8â).Interior.ColorIndex = 6 Range("A8").Font.ColorIndex = 2 "Rick Rothstein" wrote: It is always a good idea to show the code you are using for the problem area you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
I used Target.Value for two reasons... one, you showed you were using a
Change event procedure and so I guessed you were working with the Target argument that the procedure automatically references and, two, you didn't post your code originally so I didn't know what you were using for the argument to the Select Case statement. As I said originally, "It is always a good idea to show the code you are using for the problem area you are asking your question about" and the reason is because we can tailor our response to it and you won't be left scratching your head as to how to change our solutions based on a guessed-at condition. -- Rick (MVP - Excel) "JohnL" wrote in message ... Thanks Dave. I was confused with (Target.Value) but your line was very explanatory. My Worksheet Sub works like a charm now. Couldn't use your second suggestion because this isn't in a module. Thanks Rick and Dave for your time. I always learn things at this site. JohnL "Dave Peterson" wrote: Notice Rick's suggestion included ucase(). Select Case UCase(Target.Value) So you could use: Select Case ucase(Range("D6").Value) Another alternative if you don't care about any case comparisons... Add this line to the top of the module: Option Compare Text JohnL wrote: Rick, what it looks looks like is this: Select Case Range("D6").Value Case "HOUSE" Range("A8â).Interior.ColorIndex = 6 Range("A8").Font.ColorIndex = 2 "Rick Rothstein" wrote: It is always a good idea to show the code you are using for the problem area you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case sensitivity
It's in a worksheet module.
The "option compare text" will work in that kind of module, too. JohnL wrote: Thanks Dave. I was confused with (Target.Value) but your line was very explanatory. My Worksheet Sub works like a charm now. Couldn't use your second suggestion because this isn't in a module. Thanks Rick and Dave for your time. I always learn things at this site. JohnL "Dave Peterson" wrote: Notice Rick's suggestion included ucase(). Select Case UCase(Target.Value) So you could use: Select Case ucase(Range("D6").Value) Another alternative if you don't care about any case comparisons... Add this line to the top of the module: Option Compare Text JohnL wrote: Rick, what it looks looks like is this: Select Case Range("D6").Value Case "HOUSE" Range("A8â).Interior.ColorIndex = 6 Range("A8").Font.ColorIndex = 2 "Rick Rothstein" wrote: It is always a good idea to show the code you are using for the problem area you are asking your question about. My guess is for you to use the UCase function on the argument to the Select Case statement and then test the individual Case statements against upper case text. For example... Select Case UCase(Target.Value) Case "HOUSE" .... Case "ROOM" .... etc. End Select -- Rick (MVP - Excel) -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case sensitivity of functions in Excel | Excel Worksheet Functions | |||
Differentiating Case Sensitivity in a Vlookup | Excel Discussion (Misc queries) | |||
Case Sensitivity in Lookup Functions | Excel Worksheet Functions | |||
VLookup & Case Sensitivity | Excel Worksheet Functions |