Format cell to convert to Upper case

[widman]

Is there a way to format an individual cell/column so that what is typed in
that cell is automatically shown as Upper case?
ei: x7898 would show as X7898 no matter how it was typed.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in. Works on Column A
only but that can be changed to any range

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not Target.HasFormula Then
Target = UCase(Target)
End If
End If
End Sub

Mike

 

[Mike H]

Include this as the first line

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

Mike

 

[Gord Dibben]

You cannot format a cell to achieve this.

You would use a helper cell with the formula =UPPER(A1)

Or sheet event code.

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

Right-click on the sheet tab and "View Code".

Copy/paste the event code into that module.

Edit to suit........as written, works only on column A

When done editing, Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[David P.]

Gord,

I used your example once and it worked. It seems I may have done something
to where it will no longer work. Could the enable events turned off or
something? I have no clue what that means but I have almost pulled my hair
out trying to figure out why it won't do what it just did? Any thoughts on
this problem?