Validation vs Case

[Ken]

I may have previously asked this board this question ... However, I can not
seem to find it ... So if this is a duplicate post ... My apologies ...

Excel2003 ... I have a Validation List Switch (set from a range of cells)
which is working fine ... List is in UPPERCASE alpha ... You can also key in
the entry rather than use the List Switch ... this is also fine ...

Issue is ... Using the List Switch the cells in the List range are UPPERCASE
which I desire ... Alpha Value if entered directly in the cell can be entered
as lowercase which I do not desire.

So ... is there a way to set validation so only an UPPERCASE Apha character
in the List of acceptable characters can be entered in the cell?

Thanks ... Kha

 

[Dave Peterson]

Go to Google
http://groups.google.co.uk/advanced_group_search?q=group:*excel*
and specify you as the author and if you remember the subject, you can specify
that too.

 

[Biff]

You can have only 1 type of validation per cell. If your list is already in
uppercase then you probably need an event macro to change the manually typed
entries to uppercase. Unfortunately, I'm not well versed in VBA to do this
so someone else will need to help out.

Biff

 

[Gord Dibben]

Ken

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'edit cell address to suit
If Target.Address = "$A$1" Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is event code.

Right-click on the sheet tab and "View Code"

Copy/paste the above into that sheet module.


Gord Dibben MS Excel MVP