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.

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.

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.

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.

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.