Capitalize Cell Contents

[HersheyQueen]

In Excel, is there a way to format the cell so that
contents are always capitalized? I know you can do it in
Access using > but I cannot figure it out in Excel!
Thanks!

 

[Thomas]

You would have to use a a macro or as a workaround use a helper column.
In this column the formula =upper(a1) will do the trick.Copy this cell
back to your original cell to capalize it.

 

[Ken Wright]

You would need to use a change event macro such as the following:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.HasFormula = False Then
Target.Value = UCase(Target.Value)
End If
End Sub

You need to right click on the sheet tab in particular, select view code and then paste it in.
This will force all text entries to Upper case.

 

[David McRitchie]

You would need to (... that how they all start...)

If you are going to change the value with an Event macro
you should turn off Events. Also while the following does
not limit the scope to constants it will not wipe out formulas
with values. Just for the sake of argument this will not
process column 1 or row 1, you can remove them or change
to something like
If Target.Column <> 4 then Exit Sub
to restrict the capitalization to a zip state code for instance.
(US Postal two letter designation for a State)

More information in
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
My preference is to not have much of anything completely
capitalized to to invoke a regular macro when I want to make
such changes rather than having it done automatically.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- right-click on the sheettab of the corresponding
' sheet and choose 'view code'. Paste the following procedure
' in the module. -- this is for EVENT macros ONLY.
If Target.Column = 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
Application.EnableEvents = True
End Sub

Just in case you somehow manage to abend (terminate) this
macro you might have to restore events with a regular macro
'
Sub Fix_Things()
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

--

 

[Gord Dibben]

You could turn on the Caps Lock.

There is no way to format cells to UPPER Case beforehand.

You could use a helper column after the fact as in....

=UPPER(A1) entered in B1. Drag/copy down.

OR a worksheet_event macro in the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

This code is from Chip Pearson's website at.........

http://www.cpearson.com/excel/case.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002