Text formatted as All Capitals?

  • Thread starter Thread starter Joey
  • Start date Start date
J

Joey

In MSWord, formatting can easily be set so that selected text is always in
capitals by Format>Fonts>All Caps.

Can text in Excel be similarly formatted?
 
Use Upper function will be fine.
However, if you want to apply to a large area of text and want to change it
on itself, then you need some other tools, like third-party add-in, etc.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
(e-mail address removed)
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====
 
Daniel CHEN said:
Use Upper function will be fine.
However, if you want to apply to a large area of text and want to change it
on itself, then you need some other tools, like third-party add-in, etc.
Click to expand...

What, like something you sell? That is trivial VBA.
 
Try the following code (assume you are familar with VBA/macro)

Sub muUpperCase()
Dim rngText As Range, cl As Range
Set rngText = Range("A1:B10")
For Each cl In rngText
cl = UCase(cl.Value)
Next
End Sub

You can change A1:B10 to any range you want.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
(e-mail address removed)
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====
 
Daniel

One issue with your macro.

If any formulas in the range, they will be wiped out and changed to values
only.

Preferable to that would be David McRitchie's code.......

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim Cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each Cell In bigrange
Cell.Formula = UCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP
 
Joey

Not by formatting...

If you want the text in CAPS as you type it.

Use worksheet event code that changes typed text to CAPS as you enter it.

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

This code operates on Column A:F. Change the >6 to suit.

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

Copy/paste the code into that module.

If you just want to make the change after the fact, see other posts for a
macro.


Gord Dibben Excel MVP
 
Back
Top