Format cell to convert to Upper case
 

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.

Format cell to convert to Upper case
 

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

"widman" wrote:

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.

Format cell to convert to Upper case
 

Include this as the first line

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

Mike

"widman" wrote:

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.

Format cell to convert to Upper case
 

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

On Fri, 30 May 2008 14:04:00 -0700, widman
wrote:

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.

Format cell to convert to Upper case
 

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?
--
David P.


"Gord Dibben" wrote:

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

On Fri, 30 May 2008 14:04:00 -0700, widman
wrote:

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.