Custom formatting works with numbers, not with text strings.

You could use a sheet event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 1 Then Exit Sub 'adjust the 1 to your column
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste to that module. Edit column number to suit then Alt + q to
return to the Excel window.

What is typed into the column will be changed to upper case.


Gord Dibben MS Excel MVP

On Tue, 14 Apr 2009 09:53:04 -0700, Hile
wrote:

How do I FORCE the formatting of the cell to capitalize all entries w/o a
formula. I have a column for State that I've placed a validation on to only
accept 2 character entries, I now want it to capitalize whatever entry the
user types in if they don't automatically capitalize the state abbreviation.

I thought of assigning a custom format to the cell but I don't know the
syntax I need to type in for a 2 character all cap entry. The text is not in
there yet, I want it to dynamically capitalize as it is typed in.

Thank you so much. Can I use a range of columns if I want to apply this
format to more than 1 column on the sheet? If so, do I enclose in () and
separate with commas?
--
Hile


"Gord Dibben" wrote:

Custom formatting works with numbers, not with text strings.

You could use a sheet event.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column < 1 Then Exit Sub 'adjust the 1 to your column
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste to that module. Edit column number to suit then Alt + q to
return to the Excel window.

What is typed into the column will be changed to upper case.


Gord Dibben MS Excel MVP

On Tue, 14 Apr 2009 09:53:04 -0700, Hile
wrote:

How do I FORCE the formatting of the cell to capitalize all entries w/o a
formula. I have a column for State that I've placed a validation on to only
accept 2 character entries, I now want it to capitalize whatever entry the
user types in if they don't automatically capitalize the state abbreviation.

I thought of assigning a custom format to the cell but I don't know the
syntax I need to type in for a 2 character all cap entry. The text is not in
there yet, I want it to dynamically capitalize as it is typed in.

Replace If Target.Column < 1 Then Exit Sub with one of these for
contiguous or non-contiguous columns.

If Intersect(Range(Target(1).Address), _
Range("B:F")) Is Nothing Then Exit Sub


If Intersect(Range(Target(1).Address), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub


Gord

On Tue, 14 Apr 2009 11:23:01 -0700, Hile
wrote:

Thank you so much. Can I use a range of columns if I want to apply this
format to more than 1 column on the sheet? If so, do I enclose in () and
separate with commas?

I haven't followed the thread, but this would be more standard syntax:

If Intersect(Target(1), _
Range("B:F")) Is Nothing Then Exit Sub


If Intersect(Target(1), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub

Gord Dibben wrote:

Replace If Target.Column < 1 Then Exit Sub with one of these for
contiguous or non-contiguous columns.

If Intersect(Range(Target(1).Address), _
Range("B:F")) Is Nothing Then Exit Sub

If Intersect(Range(Target(1).Address), _
Range("B:B, F:F, I:I, L:L")) Is Nothing Then Exit Sub

Gord

On Tue, 14 Apr 2009 11:23:01 -0700, Hile
wrote:

Thank you so much. Can I use a range of columns if I want to apply this
format to more than 1 column on the sheet? If so, do I enclose in () and
separate with commas?

--

Dave Peterson

On Tue, 14 Apr 2009 11:23:01 -0700, Hile
wrote:

Thank you so much. Can I use a range of columns if I want to apply this
format to more than 1 column on the sheet? If so, do I enclose in () and
separate with commas?
--
Hile

In addition to Gord's suggestion, you might consider using a drop-down list
referencing a list of valid state abbreviations, instead of just allowing any
two letter string.
--ron

Good idea.

Could be used with no sheet event code but would require a DV dropdown in
every cell in multiple columns.


Gord

On Tue, 14 Apr 2009 15:05:49 -0400, Ron Rosenfeld
wrote:

On Tue, 14 Apr 2009 11:23:01 -0700, Hile
wrote:

Thank you so much. Can I use a range of columns if I want to apply this
format to more than 1 column on the sheet? If so, do I enclose in () and
separate with commas?
--
Hile

In addition to Gord's suggestion, you might consider using a drop-down list
referencing a list of valid state abbreviations, instead of just allowing any
two letter string.
--ron

On Tue, 14 Apr 2009 13:05:08 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Good idea.

Could be used with no sheet event code but would require a DV dropdown in
every cell in multiple columns.

That was my first thought. But ...

In a brief testing with Excel 2007, even with the drop down, you would still
need event code to force to Upper Case.

If, instead of picking from the drop-down, you enter the two letters manually,
(e.g. enter "ny"), the lower case "ny" will be accepted and not changed to
upper case.
--ron