Changing from lower to upper case

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

How would I set a certain region of cells so that no
matter what case the user types it always appears or
changes to upper case? Any ideas would be greatly
appreciated. Thanks. Matt
 
Hi Matt,
This code will change all text starting in cell D1 to
upper case. Adjust D1 to whatever your range is. This
will loop until the first blank cell in Col. D.

Sub trythis()
' Text already in Upper Case is ignored.

Range("D1").Select
Do Until ActiveCell = ""
ActiveCell = UCase(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

End Sub
 
Be careful with this code example
If there is a formula in the range then it will be a value after
you run this macro.

Use this one
Look at the webpages also below the macro

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

See this webpages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm
 
Ron,
Is there a way I can name the range in your example? That
way the user doesn't have to select the range and run the
macro. Thanks. Matt

-----Original Message-----
Be careful with this code example
If there is a formula in the range then it will be a value after
you run this macro.

Use this one
Look at the webpages also below the macro

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

See this webpages

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"Eva Shanley" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
Sure , example for b1:b10 on "Sheet1"
Look at David site also if you want to change the case in formulas also
http://www.mvps.org/dmcritchie/excel/proper.htm


Sub Uppercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Sheets("Sheet1").Range("b1:b10") _
.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
 
Matt

If just text is to be entered in these cells this will do the job when you hit
<ENTER> or leave the cell......

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Range("a1:a20"), Target) Is Nothing Then
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
End If
ErrHandler:
Application.EnableEvents = True
End Sub

This is worksheet_event code and must be placed into the worksheet module.

Right-click on sheet tab and "View Code". Paste in there.

Gord Dibben Excel MVP
 
The macro Ron first supplied was a generic solution working from
a selection that the user makes before running the macro.
The subsequent example used a specific range B1:B10

If you have a named range in your workbook, you can
use it and it goes within the double quotes in the same
manner.
.Range("B1:B10")
.RANGE("B:B")
.RANGE("ZipStateRange").

The use of SpecialCells automatically limits the range
to the usedrange. Reducing that selection to Text cells
eliminates processing blank cells and formulas.

Another method would be to use an Event macro, which would
only apply to the one worksheet and would be automatic, which is
what Gord supplied.

FWIW, examples on my pages
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
http://www.mvps.org/dmcritchie/excel/event.htm#uppercase
Even if you use an Event macro you might want to also install
the regular macro to fix up existing entries. Hopefully this is
only for things like zip state codes that should be capitalized,
rather than people's names.
 
Back
Top