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]...
>
> 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?
>
>
> --
> Joanie
> ------------------------------------------------------------------------
> Joanie's Profile:
> http://www.excelforum.com/member.php...o&userid=33956
> View this thread:
> http://www.excelforum.com/showthread...hreadid=537299
>

[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



In article <[email protected]>,
Joanie <[email protected]> wrote:

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

[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



"Garage YaKa" <[email protected]> wrote in message
news:[email protected]...
> 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
>