Validation for state abbreviations

[k1ngr]

How can I set up data validation so that state abbreviations are entered as
two upper case characters?

I have the abbreviations validated from a list, but it doesn't force all
uppercase.

Dick King

 

[Red]

hmmm.

I tried creating a dummy validation (aka, not a complete listing of state
abbreviations) and mine worked just fine. I pointed the validation to the
list I created (which were entered as CAPS) and therefore my drop down list
for the validation cell was populated with all caps. I tried overwriting
the cell with just lowercase inputs and I got the expected error message.
Did you make your list in all caps?

 

[Gord Dibben]

This event code will change the entries to Upper Case when selected from the DV
list.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

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

Adjust the "A1" to your DV cell address.

If more than one cell has DV list alter to Me.Range("A1, B21, C13, D1")

Copy/paste into that sheet module. Alt + q to return to the Excel sheet window.


Gord Dibben MS Excel MVP