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!

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


In article <06a601c33f17$db793fa0$(E-Mail Removed)>,
"Eva Shanley" <(E-Mail Removed)> wrote:

> 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!

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.

Eva Shanley wrote:
> 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!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

In article <(E-Mail Removed)>,
Debra Dalgleish <(E-Mail Removed)> wrote:

> 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.

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

J.E. McGimpsey wrote:
> 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.
>
> In article <(E-Mail Removed)>,
> Debra Dalgleish <(E-Mail Removed)> wrote:
>
>
>>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.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

>-----Original Message-----
>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.
>
>In article <(E-Mail Removed)>,
> Debra Dalgleish <(E-Mail Removed)> wrote:
>
>> 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.
>.
>

In article <(E-Mail Removed)>,
Debra Dalgleish <(E-Mail Removed)> wrote:

> 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!