I'm creating a key coded calender spreadsheet and would
like the user to enter all text characters in upper case.

Is their a why to ensure that all data input with text is
in upper case; all cells can be alphanumeric, but text
must be entered in uppercase.

Thanks

John

Use a helper column with:

=MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))

array entered (control shift enter). where C12 contains the input field.

Then, select C12 (input field) and go to data>validation. choose "custom"
and use the formula

=D12<91

where D12 is where you array entered the formula above. then go and setup
what you want the error message to be, etc. If any letters in the inputted
text are lower case, the data validation will catch it.

Someone correct me if I'm wrong, but a helper formula must be used here
because data validation will not recognize array entering a formula (?)..





"John" <(E-Mail Removed)> wrote in message
news:009701c4a63b$840d4540$(E-Mail Removed)...
> I'm creating a key coded calender spreadsheet and would
> like the user to enter all text characters in upper case.
>
> Is their a why to ensure that all data input with text is
> in upper case; all cells can be alphanumeric, but text
> must be entered in uppercase.
>
> Thanks
>
> John

Hi Dave
don't like to correct you but you could use the following formula
directly in the data validation dialog:
=AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
1)))<=90)

I think Harlan posted ths some time ago

--
Regards
Frank Kabel
Frankfurt, Germany

"Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> Use a helper column with:
>
> =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
>
> array entered (control shift enter). where C12 contains the input
field.
>
> Then, select C12 (input field) and go to data>validation. choose
"custom"
> and use the formula
>
> =D12<91
>
> where D12 is where you array entered the formula above. then go and
setup
> what you want the error message to be, etc. If any letters in the
inputted
> text are lower case, the data validation will catch it.
>
> Someone correct me if I'm wrong, but a helper formula must be used
here
> because data validation will not recognize array entering a formula
(?)..
>
>
>
>
>
> "John" <(E-Mail Removed)> wrote in message
> news:009701c4a63b$840d4540$(E-Mail Removed)...
> > I'm creating a key coded calender spreadsheet and would
> > like the user to enter all text characters in upper case.
> >
> > Is their a why to ensure that all data input with text is
> > in upper case; all cells can be alphanumeric, but text
> > must be entered in uppercase.
> >
> > Thanks
> >
> > John
>
>

Frank, I have not taken time to dissect that quite yet, but when I try it,
it remains TRUE no matter if I put (in A1):

apple, APPLE, Apple, aPPLE




"Frank Kabel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Dave
> don't like to correct you but you could use the following formula
> directly in the data validation dialog:
> =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> 1)))<=90)
>
> I think Harlan posted ths some time ago
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
> > Use a helper column with:
> >
> > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
> >
> > array entered (control shift enter). where C12 contains the input
> field.
> >
> > Then, select C12 (input field) and go to data>validation. choose
> "custom"
> > and use the formula
> >
> > =D12<91
> >
> > where D12 is where you array entered the formula above. then go and
> setup
> > what you want the error message to be, etc. If any letters in the
> inputted
> > text are lower case, the data validation will catch it.
> >
> > Someone correct me if I'm wrong, but a helper formula must be used
> here
> > because data validation will not recognize array entering a formula
> (?)..
> >
> >
> >
> >
> >
> > "John" <(E-Mail Removed)> wrote in message
> > news:009701c4a63b$840d4540$(E-Mail Removed)...
> > > I'm creating a key coded calender spreadsheet and would
> > > like the user to enter all text characters in upper case.
> > >
> > > Is their a why to ensure that all data input with text is
> > > in upper case; all cells can be alphanumeric, but text
> > > must be entered in uppercase.
> > >
> > > Thanks
> > >
> > > John
> >
> >
>

Ok - I can take out the UPPER(A1) part, but then in order for it to look at
anything but the very first letter, it needs to be array entered, and I
don't think that can be done in the data validation section, can it?




"Frank Kabel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Dave
> don't like to correct you but you could use the following formula
> directly in the data validation dialog:
> =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> 1)))<=90)
>
> I think Harlan posted ths some time ago
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> news:(E-Mail Removed)...
> > Use a helper column with:
> >
> > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
> >
> > array entered (control shift enter). where C12 contains the input
> field.
> >
> > Then, select C12 (input field) and go to data>validation. choose
> "custom"
> > and use the formula
> >
> > =D12<91
> >
> > where D12 is where you array entered the formula above. then go and
> setup
> > what you want the error message to be, etc. If any letters in the
> inputted
> > text are lower case, the data validation will catch it.
> >
> > Someone correct me if I'm wrong, but a helper formula must be used
> here
> > because data validation will not recognize array entering a formula
> (?)..
> >
> >
> >
> >
> >
> > "John" <(E-Mail Removed)> wrote in message
> > news:009701c4a63b$840d4540$(E-Mail Removed)...
> > > I'm creating a key coded calender spreadsheet and would
> > > like the user to enter all text characters in upper case.
> > >
> > > Is their a why to ensure that all data input with text is
> > > in upper case; all cells can be alphanumeric, but text
> > > must be entered in uppercase.
> > >
> > > Thanks
> > >
> > > John
> >
> >
>

Hi Dave
sorry forgot to delete the UPPER part. Use:
=AND(MIN(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),
1)))>=65,MAX(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),
1)))<=90)

The previous one checked for a-z and A-Z. This one works for me in the
data validation dialog

--
Regards
Frank Kabel
Frankfurt, Germany

"Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
news:ujtAk#(E-Mail Removed)...
> Frank, I have not taken time to dissect that quite yet, but when I
try it,
> it remains TRUE no matter if I put (in A1):
>
> apple, APPLE, Apple, aPPLE
>
>
>
>
> "Frank Kabel" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Dave
> > don't like to correct you but you could use the following formula
> > directly in the data validation dialog:
> > =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > 1)))<=90)
> >
> > I think Harlan posted ths some time ago
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> > news:(E-Mail Removed)...
> > > Use a helper column with:
> > >
> > > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
> > >
> > > array entered (control shift enter). where C12 contains the input
> > field.
> > >
> > > Then, select C12 (input field) and go to data>validation. choose
> > "custom"
> > > and use the formula
> > >
> > > =D12<91
> > >
> > > where D12 is where you array entered the formula above. then go
and
> > setup
> > > what you want the error message to be, etc. If any letters in the
> > inputted
> > > text are lower case, the data validation will catch it.
> > >
> > > Someone correct me if I'm wrong, but a helper formula must be
used
> > here
> > > because data validation will not recognize array entering a
formula
> > (?)..
> > >
> > >
> > >
> > >
> > >
> > > "John" <(E-Mail Removed)> wrote in message
> > > news:009701c4a63b$840d4540$(E-Mail Removed)...
> > > > I'm creating a key coded calender spreadsheet and would
> > > > like the user to enter all text characters in upper case.
> > > >
> > > > Is their a why to ensure that all data input with text is
> > > > in upper case; all cells can be alphanumeric, but text
> > > > must be entered in uppercase.
> > > >
> > > > Thanks
> > > >
> > > > John
> > >
> > >
> >
>
>

Hi
it can (sometimes Excel is better than one thinks...)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
news:eO#(E-Mail Removed)...
> Ok - I can take out the UPPER(A1) part, but then in order for it to
look at
> anything but the very first letter, it needs to be array entered, and
I
> don't think that can be done in the data validation section, can it?
>
>
>
>
> "Frank Kabel" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Dave
> > don't like to correct you but you could use the following formula
> > directly in the data validation dialog:
> > =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > 1)))<=90)
> >
> > I think Harlan posted ths some time ago
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> > news:(E-Mail Removed)...
> > > Use a helper column with:
> > >
> > > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
> > >
> > > array entered (control shift enter). where C12 contains the input
> > field.
> > >
> > > Then, select C12 (input field) and go to data>validation. choose
> > "custom"
> > > and use the formula
> > >
> > > =D12<91
> > >
> > > where D12 is where you array entered the formula above. then go
and
> > setup
> > > what you want the error message to be, etc. If any letters in the
> > inputted
> > > text are lower case, the data validation will catch it.
> > >
> > > Someone correct me if I'm wrong, but a helper formula must be
used
> > here
> > > because data validation will not recognize array entering a
formula
> > (?)..
> > >
> > >
> > >
> > >
> > >
> > > "John" <(E-Mail Removed)> wrote in message
> > > news:009701c4a63b$840d4540$(E-Mail Removed)...
> > > > I'm creating a key coded calender spreadsheet and would
> > > > like the user to enter all text characters in upper case.
> > > >
> > > > Is their a why to ensure that all data input with text is
> > > > in upper case; all cells can be alphanumeric, but text
> > > > must be entered in uppercase.
> > > >
> > > > Thanks
> > > >
> > > > John
> > >
> > >
> >
>
>

So it changes from TRUE/FALSE when it's under data validation, but not when
it's pasted in B1.. noted.


"Frank Kabel" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
> it can (sometimes Excel is better than one thinks...)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> news:eO#(E-Mail Removed)...
> > Ok - I can take out the UPPER(A1) part, but then in order for it to
> look at
> > anything but the very first letter, it needs to be array entered, and
> I
> > don't think that can be done in the data validation section, can it?
> >
> >
> >
> >
> > "Frank Kabel" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hi Dave
> > > don't like to correct you but you could use the following formula
> > > directly in the data validation dialog:
> > > =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > > 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
> > > 1)))<=90)
> > >
> > > I think Harlan posted ths some time ago
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> > > news:(E-Mail Removed)...
> > > > Use a helper column with:
> > > >
> > > > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
> > > >
> > > > array entered (control shift enter). where C12 contains the input
> > > field.
> > > >
> > > > Then, select C12 (input field) and go to data>validation. choose
> > > "custom"
> > > > and use the formula
> > > >
> > > > =D12<91
> > > >
> > > > where D12 is where you array entered the formula above. then go
> and
> > > setup
> > > > what you want the error message to be, etc. If any letters in the
> > > inputted
> > > > text are lower case, the data validation will catch it.
> > > >
> > > > Someone correct me if I'm wrong, but a helper formula must be
> used
> > > here
> > > > because data validation will not recognize array entering a
> formula
> > > (?)..
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "John" <(E-Mail Removed)> wrote in message
> > > > news:009701c4a63b$840d4540$(E-Mail Removed)...
> > > > > I'm creating a key coded calender spreadsheet and would
> > > > > like the user to enter all text characters in upper case.
> > > > >
> > > > > Is their a why to ensure that all data input with text is
> > > > > in upper case; all cells can be alphanumeric, but text
> > > > > must be entered in uppercase.
> > > > >
> > > > > Thanks
> > > > >
> > > > > John
> > > >
> > > >
> > >
> >
> >
>

John

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

Copy/paste the event code to that module.

As written, it operates on the first 8 columns.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP

On Wed, 29 Sep 2004 08:46:42 -0700, "John"
<(E-Mail Removed)> wrote:

>I'm creating a key coded calender spreadsheet and would
>like the user to enter all text characters in upper case.
>
>Is their a why to ensure that all data input with text is
>in upper case; all cells can be alphanumeric, but text
>must be entered in uppercase.
>
>Thanks
>
>John

Hi Frank

bit confused ... should the formula be array entered in Data Validation? it
doesn't seem to matter when i'm using it.

Cheers
JulieD

"Frank Kabel" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
> it can (sometimes Excel is better than one thinks...)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
> news:eO#(E-Mail Removed)...
>> Ok - I can take out the UPPER(A1) part, but then in order for it to
> look at
>> anything but the very first letter, it needs to be array entered, and
> I
>> don't think that can be done in the data validation section, can it?
>>
>>
>>
>>
>> "Frank Kabel" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Hi Dave
>> > don't like to correct you but you could use the following formula
>> > directly in the data validation dialog:
>> > =AND(MIN(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
>> > 1)))>=65,MAX(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),
>> > 1)))<=90)
>> >
>> > I think Harlan posted ths some time ago
>> >
>> > --
>> > Regards
>> > Frank Kabel
>> > Frankfurt, Germany
>> >
>> > "Dave R." <(E-Mail Removed)> schrieb im Newsbeitrag
>> > news:(E-Mail Removed)...
>> > > Use a helper column with:
>> > >
>> > > =MAX(CODE(MID(C12, ROW(INDIRECT("1:"&LEN(C12))),1)))
>> > >
>> > > array entered (control shift enter). where C12 contains the input
>> > field.
>> > >
>> > > Then, select C12 (input field) and go to data>validation. choose
>> > "custom"
>> > > and use the formula
>> > >
>> > > =D12<91
>> > >
>> > > where D12 is where you array entered the formula above. then go
> and
>> > setup
>> > > what you want the error message to be, etc. If any letters in the
>> > inputted
>> > > text are lower case, the data validation will catch it.
>> > >
>> > > Someone correct me if I'm wrong, but a helper formula must be
> used
>> > here
>> > > because data validation will not recognize array entering a
> formula
>> > (?)..
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > "John" <(E-Mail Removed)> wrote in message
>> > > news:009701c4a63b$840d4540$(E-Mail Removed)...
>> > > > I'm creating a key coded calender spreadsheet and would
>> > > > like the user to enter all text characters in upper case.
>> > > >
>> > > > Is their a why to ensure that all data input with text is
>> > > > in upper case; all cells can be alphanumeric, but text
>> > > > must be entered in uppercase.
>> > > >
>> > > > Thanks
>> > > >
>> > > > John
>> > >
>> > >
>> >
>>
>>
>