Format Cell for All Cap Text

[scrabtree]

I need to format cells so it will convert all text entry
to caps.

 

[Tom Ogilvy]

Sub tester9()
Dim rng As Range
Set rng = Cells.SpecialCells(xlConstants, xlTextValues)
For Each cell In rng
cell.Value = StrConv(cell.Value, vbUpperCase)
Next

End Sub

 

[Gord Dibben]

scrab

You can't format cells in this manner.

You can keep the Caps Lock on or use code.

Tom O. has given code to change "after the fact".

This worksheet event code will change to Upper case when you exit the cells.

As written works in Columns A through C.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 3 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP