How to force upper case entries

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a spreadsheet where entry is controlled by dropdown
lists. Mostly no problem, but one dropdown has only a
single letter and the users (bless them) have started
typing the letter and tabbing to the next entry. This is
OK if they are in uppercase but Excel throws a wobbly if
they enter a lowercase letter. i.e. F is OK f throws an
error.

How do I force an uppercase letter entry? (Can I switch
their keyboard to caps lock?)

Thanks Paul
 
Paul,

This VBA sets capslock on or off

Declare Function GetKeyboardState Lib "user32" _
(pbKeyState As Byte) As Long
Declare Function SetKeyboardState Lib "user32" _
(lppbKeyState As Byte) As Long

Sub SetCapsLock(Optional onState As Boolean = True)
Dim nResult As Long
Dim sKB(0 To 255) As Byte

nResult = GetKeyboardState(sKB(0))
If onState = True Then
sKB(&H14) = 1
Else
sKB(&H14) = 0
End If
nResult = SetKeyboardState(sKB(0))

End Sub

But this applies to everything, so a better approach might be to allow
lower-case in the dropdown, and then use worksheet change event code to
upshift it.
 
Maybe this would work for you:
(Found it in Google)

Using Data Validation:(choose custom)

=EXACT(A1,UPPER(A1))

A solution find by Bob Umlas

HTH
 
One possibility is to use data validation to allow only
upper case characters. Select the cell, say A1. From the
menu select Data then Validation. In the Settings tab,
select Custom from the allow list. Enter in the formula
text =AND(CODE(A1)>=65,CODE(A1)<=90). You can also enter
an error message if you like.
 
Back
Top