can you ensure that all text entry is in uppercase

[John]

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

 

[Dave R.]

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 (?)..

 

[Frank Kabel]

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

 

[Dave R.]

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

 

[Dave R.]

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]

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

 

[Frank Kabel]

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

 

[Dave R.]

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

 

[Gord Dibben]

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

 

[JulieD]

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

 

[Peo Sjoblom]

Julie,

no need to array enter when doing validation or conditional formatting

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[JulieD]

Hi Peo

thanks - was just getting a tad confused

JulieD

 

[hgrove]

Frank Kabel wrote...
...

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

Frank's formula won't work reliably for the OP. As the OP states, cell
content may be alphaNUMERIC. Franks formula above will return FALSE if
the cell contains any digits.

While I may have posted something like this in the past, we all make
mistakes. It'd be much simpler to check for all caps using

=EXACT(A1,UPPER(A1))

which would snag only lower case letters. If only upper case letters
and decimal digits should be allowed, better to use

=AND(ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Note that Data > Validation is easily circumvented by pasting into
cells.

 

[hgrove]

Peo Sjoblom wrote...
...

no need to array enter when doing validation or conditiona

formatting
...

Also not possible to do so. Entering validation or conditiona
formatting formulas, or even definitions of defined names, there's bot
no way to enter such formulas as arrays and no need to do so. Fo
everything other than entry in cells, Excel seems to interpret formula
as array formulas.

The real question should be why array formula entry is ever needed
FWLIW, Applixware's Spreadsheet provides array formula functionalit
without requiring different entry mode than normal, single cel
formulas. I suspect this is yet another instance of the antiquate
formula parser imposing unnecessary requirements

 

[hgrove]

Gord Dibben wrote...
...

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

...

Inflexible. Better to allow the user to specify the range to be checke
by making it a worksheet-level defined name like Entry_ForceUCase. I
the user selects the range in which all letters must be convertedd t
upper case and defines that range as Entry_ForceUCase, then the use
could use the Change event handler


Code
-------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Names("Entry_ForceUCase").RefersToRange) Is Nothing _
Or Target.HasFormula Or IsNumeric(Evaluate(Target.Formula)) Then Exit Sub
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End Su

 

[Frank Kabel]

Frank Kabel wrote...
..

Frank's formula won't work reliably for the OP. As the OP states, cell
content may be alphaNUMERIC. Franks formula above will return FALSE if
the cell contains any digits.

Hi Harlan
thanks for the correction. missed the 'numeric' part in the OP's post.

While I may have posted something like this in the past, we all make
mistakes. It'd be much simpler to check for all caps using

=EXACT(A1,UPPER(A1))

oh yes. Much simpler.... Sometimes one misses the easiest solution


Regards
Frank