MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I change all text to CAPS?

Posted by Rob on July 13, 2001 8:15 AM

It must be able to be done, but I can't figure it out.

Posted by Colm on July 13, 2001 8:39 AM

Hi Rob,

If you download the free evaluation copy of the spreadsheet assistant at

and then install it adds features to excel and one is where you can select cells Click format and then change letter case and it gives you a number of options like Capitalize all letters or the convert to proper case.

The download also adds lots of other cool features


Posted by Connie on July 13, 2001 8:56 AM

Rob, see this earlier post and the proposed solutions.


Posted by Joe Was on July 13, 2001 9:57 AM

Re: How do I change all text to CAPS code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCells As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns("B:C")) Is Nothing Then
For Each rCells In Intersect(Target, Columns("B:C"))
rCells = UCase(rCells.Text)
End If
Application.EnableEvents = True
End Sub

This code is to be added to the Sheet-Tab View-code macro page, the name cannot be changed or the code will fail!

The code will change Columns B & C to upper case. The code can be changed to change a cell, row or range as well! JSW

Posted by Laura Luke on July 13, 2001 11:08 AM

Re: How do I change all text to CAPS code.

Try the following code:

This code allows you to toggle through all lower case, sentence case, and all caps.

Sub ToggleCase()
Dim rng As Range
For Each rng In Selection.Cells
Select Case True
Case rng = LCase(rng)
rng = UCase(rng)
Case rng = UCase(rng)
rng = Application.Proper(rng)
Case Else
rng = LCase(rng)
End Select
End Sub

Hope this helps!