MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Coverting to all caps


Posted by Jeff on August 16, 2001 2:04 PM

I need to make sure the contents of a text cell are all caps. Can anyone point me in the right direction?
Thanks,
Jeff


Posted by Dax on August 16, 2001 2:21 PM

One cell or the whole sheet?

Try this for all cells.

Right click the worksheet tab and select View Code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

However, this has the limitation that it will only work if the user is changing a single cell. If the user tries to change more than one cell at a time(e.g. paste into 10 cells) then the code will crash.

For a single cell, say A5, then use this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$5" Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End Sub

Hope it helps,
Dax.

Posted by Jeff on August 17, 2001 8:18 AM

Thank you! I got it to work perfectly!
Jeff