Change case of entered text

[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?

 

[Chip Pearson]

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]...

 

[JE McGimpsey]

See

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

If you're not familiar with macros, see


http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Garage YaKa]

Convert to Uppercase in colonne B When entered

http://cjoint.com/?eCvqjXuqjI

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

Cordially JB

 

[Chip Pearson]

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