UPPER as default

  • Thread starter Thread starter Brenda Hutton
  • Start date Start date
B

Brenda Hutton

A user wants to setup a worksheet with the default in several of the columns
to be All Caps text. I can apply UPPER to a cell and drag that down for who
knows how many rows. Is there a better way?
 
Hi Brenda

Select Column A
Data Validation:(choose custom)

=EXACT(A1,UPPER(A1))

You can only typer in Uppercase now
 
Or this if you want to change the text in the cells to Uppercase when you press Enter.
this will work only in Column A.
You must place this event in a sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub


See this webpages also

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm

Here is a macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
I mean column C

If Target.Column = 3 Then

Ron de Bruin said:
Or this if you want to change the text in the cells to Uppercase when you press Enter.
this will work only in Column A.
You must place this event in a sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub


See this webpages also

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm

Here is a macro for changing text cells in the selection

Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = UCase(cel.Value)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Click to expand...
 
OK, I see how that works. However, is there something different about doing
it as Data Validation as opposed to using the UPPER function and dragging
the cell handle down hundreds or thousands of rows? Being as it is a
database type use and you want all the text in Columns A, B, and D to be
upper case, it seems like you are selecting rows in either case with the
possibility of data which extends beyond those rows not having the attribute
applied to them.
 
If you type the formula
=UPPER(A1)
in a cell in another column and copy down, you are converting what is
already in column A, or what may be put in here later, into upper case in
that other column. You need one formula for each row.

If you use data validation on column A, you are preventing someone from
entering data in that column unless it is in upper case. You can validate
the whole column with one equation. Note, however, that this does not change
what is already in the column, and only prevents entry by typing in lower
case; you can still copy/paste lower case into this column.
 
Thanks Ron. Think I'm having MAJOR brain fade today. This is exactly what
I wanted. I could not get my thoughts off of a data validation LIST...
 
Now the user tells me they want data validation not only for the UPPER case
entry, but for the allowable data. Is it possible to have multiple data
validations for the same cells?
 
What is the allowable data? You can have multiple conditions.
For instance if the allowable data is BRENDA in upper case you can use

=(EXACT(A1,UPPER(A1))*(A1="Brenda"))
 
Brenda

You could use the List option in Data Validation. This lets you type a list
of allowable entries.

Andy.
 
There is a list of allowable options: 1,2,5,10,EW,JK and the like. What we
are now wanting to do is to combine that with the condition that all text is
UPPER case. How is that done, or is it possible?
 
Brenda

If all of your items in the list are upper case, then that's the only choice
they have!! If it's not in the list, they can't choose it.

Andy.
 
You are correct, only other way would be to use a formula and validate
each entry from the list like

=OR(A1=1,A1=2,A1=5,A1=10,EXACT("EW",A1))

using allow custom
 
OK, let's try this approach. Is it feasible to have the validation list,
which restricts entries, and a formula in the cell such as
=exact(A1,UPPER(A1)) ?? I understand I would have to manually copy the
formula down to account for the greatest numbers of rows I would expect to
be used.
 
Brenda

Could you use an Event macro in the worsksheet to change text to UPPER as it
is entered and use Data Validation for the allowed input?

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

All cells in columns A through H will be forced to UPPER as text is entered.

Ron de Bruin suggested similar much earlier in this thread.

Gord Dibben Excel MVP
 
Gord, I know next to nothing about macros in Excel, but I do have a resource
here in the office that may be able to help me out with what you have
written here.

Status now:
We have a list. Validation is setup as LIST and SOURCE refers to the range
containing the list. Users must use one of the listed selections but case
is not converted to upper.

I'm going to fudge my way around and see what I can do with your macro.
Feel free to jump in with any details I may need. Thank you SO MUCH for
your help.

Brenda
 
Hi Brenda

This is not a macro but a event.
The event will run automatic when you Change a cell in
columns A through H in Gord's example.

Right click on a sheet tab and choose view code
Paste the code there
Alt-Q to go back to Excel

Post back if you need more help
 
Back
Top