i want a case sensitivity check for List values
hi,
i have a list of values something like this..
Priority
Blocking
High
Medium
Low
i have deifned a name for this list. A column of cells can contain only these values, user have been provided dropdown for selecting from this list.
but the problem is...if a user types "blocking" instead of selecting "Blocking" from the dropdown, :eek: the cell takes a value as "blocking" and never prompt it as an error. I have written a perl script that reads these values and puts them in the database. the database integrity goes for a toss if this value is put in it.
Is there a way or a macro(that can be written)to provide a check for case sensitivity...?
I also have other lists that dont fit the PROPER, UPPER OR LOWER case
the list can be a mixture of all these..
to make matters worse i am new to using excel and have very faint idea about writing excel macros.. :o
Please HELP...
[947 byte] By [
riz.pro] at [2007-11-11 10:27:20]
# 2 Re: i want a case sensitivity check for List values
hi benjamin,
i have used lists in the sheet, if a user types in a value, which is not in the list the application throws an error, but if the user types a value that is present in the list, in a different CASE, it does not throw an error..the application simply takes the value...
for example
the list contains
Blocking
High
Medium
Low
note the case here..
if the user enters something other than the values above..its an error
but if the user types "BLOcking" instead of "Blocking" Excel does not throw an error...
# 3 Re: i want a case sensitivity check for List values
Perhaps you can write a small macro to convert the input data, for example:
dim strValue as string
strValue = range("A1").value 'Ex BLOcking
strValue = ucase(strvalue) 'Now BLOCKING
strValue = left(strvalue,1) & lcase(mid(strvalue,2)) 'Now Blocking
Perhaps you can do this also in pearl, I don't know.
# 5 Re: i want a case sensitivity check for List values
hi,
i managed to write a macro, might be crude but it serves the purpose.
the macro reads the value from the current cell and finds the same value in the list values(case insensitive check) then it replaces the value with the value from the list.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
curr_value = Target.Value
curr_address = Target.Address
col = Target.Column
Application.Goto reference:="mapping" 'a cell below which i have the names of the list and the column no
Cells.Find(what:=col, after:=ActiveCell, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=True).Activate
Range(ActiveCell.Address).Offset(0, 1).Select
Range(ActiveCell.Value).Select
Range(ActiveCell.Address).Offset(-1, 0).Select
Cells.Find(what:=curr_value, after:=ActiveCell, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=False).Activate
replacement_value = ActiveCell.Value
Range(curr_address).Select
ActiveCell.Value = replacement_value
'Application.ScreenUpdating = True
End If
End Sub
thanx for ur help...this is the first Excel macro that i have written
