Validation Drop Down Box

[Barbara Ryan]

I have a validation drop down box for a cell. Let's say the valid values are
"BLUE", "Red", and "YELLOW". If I type in "blue" (lowercase), I want the
cell to find the value and display it in the correct case (i.e., "BLUE").
If I type "RED", I want the cell displayed as "Red"

How do I accomplish this?

Thanks,
Barb Ryan

 

[lexcel]

Hi Barbara,

If you just make a list and a cell with a list-type validation
referring to this list, excel does what you want automatically: It will
accept text non-case sensitive and display it as found in the list.

If you want to do any other data manipulation you need to use a
worksheet macro.

Here is an example:
Put BLUE in C1, Red in C2 and YELLOW in c3
Select C1-C3. Name this range MyList : Click the name field (left of
the function field fx), which displays C1, replace this with "MyList"
en press return.
Pick any cell and name it "ValiCell"
Create a list validation for this cell and put MyList as the source.
Right-click on the sheet tab, click "View Code" and insert the
following code
________________________________________________________________________

Private Sub Worksheet_Change(ByVal Target As Range)
Dim index As Integer, Text As String
Static Semaphore As Boolean

If Semaphore Then Exit Sub ' To avoid recursion
Semaphore = True

' Check if it is the cell with the list validation that has been
changed
If Target.Address = Range("ValiCell").Address Then
index = 0 ' if not found n will be 0
On Error Resume Next ' to avoid error message when not
found
index = WorksheetFunction.Match(Target, Range("MyList"), 0) '
lookup in the list
If index <> 0 Then ' If found in list then do
something
Text = Range("MyList")(index) ' Get corresponding list
item
Target.Value = UCase(Left(Text, 1)) & LCase(Mid(Text, 2)) '
End If
Target.Interior.ColorIndex = Array(2, 5, 3, 6)(index)
End If
Semaphore = False
End Sub



___________________________________________________________________

The above code should display the input value starting with a capital
and the rest in lower case and adjust the color of the cell.
If you replace the code within the If statement by :

index = index Mod Range("MyList").Count + 1
Target.Value = Range("MyList")(index) ' Get next
list item

then the list item after the item the user has chosen or input will be
displayed.

I hope you will have as much fun as I had playing with this stuff.

 

[barbararyan]

Thanks for your response, although I am not having as much fun with this
as you have!

I tried to just make a list and a cell with a list-type validation;
however, if I type in a value, it remains in the case which it was
entered.

This is my code to add validation:

With Cells(ActiveCell.Row, intCol).Validation
..Delete
..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & strRange

strRange is a named range containing allowable values.

 

[lexcel]

Hi barbara,

From this little piece of code I understand that you create a

validation list "on the fly" and always in column intCol.
For the next piece of code to work you need to make intCol public, so
declare it above your code as

public intCol as integer

and remove the old dim intCol

insert the next piece of code in your worksheet module in the way I
described in the former post:
right-click the tab of the sheet and select "View code", then copy and
paste the next piece of code into the worksheet module:

'----------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim index As Integer

Application.EnableEvents = False

' --- Check if it is the cell with the list validation that has been
changed

If Target.Column = intCol Then
index = 0 ' if not found n will be 0
On Error Resume Next ' to avoid error message when not
found
index = WorksheetFunction.Match(Target, Range("strRange"), 0)
' lookup in the list
If index Then ' If found in list then do something
Target.Value = Range("strRange")(index) ' Get
corresponding list item
End If
End If
Application.EnableEvents = True

End Sub
'---------------------------------------------------------------------------------------

I tested it and it appears that it only works if strRange is on the
same sheet as the cell with the list. If you need it to be on another
sheet, we'll need to find another solution.

If this still doen't help, send me the sheet and I will fix it.

Greetz,

Lex