Data Validation

[Eva Shanley]

I have a list of names typed in capital letters that I'm
using in data validation to restrict entries. However, a
user can type a name from the list in lower case letters
(instead of selecting from the drop-down list), and the
lower case will be accepted. Is there a way to restrict
to upper case only? Thanks for any help!

 

[J.E. McGimpsey]

First, is there really a problem with entering lower case? Some
specifications call for all upper, but they tend to be much less
readable. Very few XL functions are case sensitive, and for those
that are, you can wrap cell references in Upper() to coerce the text
to upper case.

That said, one way to restrict entries to upper case is to use a
Worksheet_Change event macro. Put this in the worksheet code module
(right-click on the worksheet tab, choose View Code, paste the code
in the window that opens, then click the XL icon on the toolbar to
return to XL):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(False, False) = "A1" Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End Sub

 

[Debra Dalgleish]

Another option, if the list is short, is to type the entries in the Data
Validation Source box. For example: JAN,FEB,MAR

This will restrict the entries to an exact match to list items.

 

[J.E. McGimpsey]

Note that if you do this, you should somewhere (like in the Error
Alert) indicate that case matters - it's probably not intuitively
obvious to most people why "Jan" is invalid when JAN is on the list.

 

[Debra Dalgleish]

Ahhh, J.E., you take all the fun out of spreadsheet design!

 

[Eva Shanley]

Thanks to both Debra and J.E. for your solutions. I ended
up using Debra's because the list was short; also I'm a
bit thick when it comes to VB and I could not get the code
to work right. It was important these entries be upper
case because the worksheet will create a .txt file that
feeds another program that does care about upper case.
Thanks again.

 

[J.E. McGimpsey]

Ahhh, J.E., you take all the fun out of spreadsheet design!

Gee, that's not what the principal of my son's school says about the
enrollment, budget and cash flow models I developed for her (I'm a
director of the school's board). She has *days* of fun every quarter
or so!