convert text in excel to uppercase

[Guest]

Is it possible to convert all text in a workbook or on a spreadsheet to all
uppercase?

 

[Guest]

In VBA Editor

Sub MakeUpper()
Dim MySht As Worksheet, MyCell As Range
For Each MySht In ThisWorkbook.Sheets
For Each MyCell In MySht.UsedRange.Cells
MyCell = UCase(MyCell)
Next
Next
End Sub

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/case.htm

 

[David McRitchie]

There is no such example for this on Chip's page, but you
probably want to change the
MyCell = UCase(MyCell)
to
MyCell.formula = UCase(MyCell.formula)
so you don't wipe out formulas.

My own solution would be "Back to Kindergarten" in
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which should be considerably, and ignores formulas and
empty cells. Your use of UsedRange will eliminate the
vast ocean of empty cells, but not those within the used range.

 

[Gord Dibben]

Elaine

A warning if you use this MakeUpper macro.

All formulas will be converted to values.

May not be a desired result.

An alternative......

Sub Upper_All_Sheets()
'David McRitchie
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As String, ws As Object, Cell As Range
x = MsgBox("Use CANCEL to abort changing all constant " _
& "cells to uppercase", vbOKCancel)
If x = vbCancel Then Exit Sub
For Each ws In ActiveWorkbook.Sheets
On Error Resume Next 'In case no cells in selection
ws.Activate
For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
Cell.Value = UCase(Cell.Value)
Next
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP

 

[Gord Dibben]

Sorry about that David

In my posting I changed your "Kindergarten" sub to "Upper_All_Sheets"

Gord Dibben Excel MVP