Auto-Capitalizing

[helga1980]

I found some code to auto-capitlize in a worksheet...what do I have to do so that it doesn't apply the auto-capitalization of the existing text to the whole sheet??

Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbUpperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub

Thanks!

 

[stnkynts]

Change the range at this point here.

For Each Rng In [COLOR=red]Selection.SpecialCells(xlCellTypeConstants, _[/COLOR]
[COLOR=red]xlTextValues).Cells[/COLOR]

The code you posted works off of the selected range prior to running the macro. You could change the range to be, for example, A1:A50.

Ex:

Sub ChangeCase()
Dim Rng As Range, myRange as Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
Set myRange = Range("A1:A50")
For Each Rng In myRange.SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbUpperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub

 

[helga1980]

Perfect, thanks stnkynts!