Change case of entered text

  • Thread starter Thread starter Joanie
  • Start date Start date
J

Joanie

I have an extensive spreadsheet and would like a simple way to convert
all of the text entered into column B to uppercase. Right now it is a
mixture of upper and lower. Any help?
 
Try the following macro:

Sub ConvertToUpper()
Dim Rng As Range
Application.EnableEvents = False
For Each Rng In Application.Intersect(ActiveSheet.UsedRange, _
Range("B:B")).SpecialCells(xlCellTypeConstants)
Rng.Value = UCase(Rng.Text)
Next Rng
Application.EnableEvents = True
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Joanie" <[email protected]>
wrote in message
news:[email protected]...
 
You should set EnableEvents to False in this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 2 And Target.Count = 1 Then
Target = UCase(Target)
End If
Application.EnableEvents = True
End Sub

Without turning off EnableEvents, the Change code will change a
cell, which causes Change to run again, which changes a cell,
which caues Change to run again, and so on and on.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top