Formatting cell for state abbreviations
 

I am unable to format cell to accept ME, the abbreviation for Maine. It
continues to revert to a lowercase "E" as in "Me" while other states such as
NH, VA and LA maintain their uppercase second letter. What am I doing wrong?
Thank you
--
LovesArt247

Formatting cell for state abbreviations
 

This is a AutoCorrect item for people who mistype the word "Me" (as in me
myself)
Tools | Autocorrect; locate and remove this entry -- remember it will effect
all Office apps
OR: after Excel gives you Me, use CTRL+Z to undo
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LovesArt247" wrote in message
...
I am unable to format cell to accept ME, the abbreviation for Maine. It
continues to revert to a lowercase "E" as in "Me" while other states such
as
NH, VA and LA maintain their uppercase second letter. What am I doing
wrong?
Thank you
--
LovesArt247

Formatting cell for state abbreviations
 

Hi Bernard,

In Word you can press Undo to back out of the AutoCorrect, however, in Excel
you will end up removing the ME. The solution is to press ME "space" and
then press Undo and Enter.

By the way I checked back as far as office 2000 and ME is not in AutoCorrect
by default.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Bernard Liengme" wrote:

This is a AutoCorrect item for people who mistype the word "Me" (as in me
myself)
Tools | Autocorrect; locate and remove this entry -- remember it will effect
all Office apps
OR: after Excel gives you Me, use CTRL+Z to undo
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LovesArt247" wrote in message
...
I am unable to format cell to accept ME, the abbreviation for Maine. It
continues to revert to a lowercase "E" as in "Me" while other states such
as
NH, VA and LA maintain their uppercase second letter. What am I doing
wrong?
Thank you
--
LovesArt247

Formatting cell for state abbreviations
 

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.
--
Hile


"Bernard Liengme" wrote:

This is a AutoCorrect item for people who mistype the word "Me" (as in me
myself)
Tools | Autocorrect; locate and remove this entry -- remember it will effect
all Office apps
OR: after Excel gives you Me, use CTRL+Z to undo
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"LovesArt247" wrote in message
...
I am unable to format cell to accept ME, the abbreviation for Maine. It
continues to revert to a lowercase "E" as in "Me" while other states such
as
NH, VA and LA maintain their uppercase second letter. What am I doing
wrong?
Thank you
--
LovesArt247

Formatting cell for state abbreviations
 

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.

Formatting cell for state abbreviations
 

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.

Formatting cell for state abbreviations
 

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

Formatting cell for state abbreviations
 

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?

Formatting cell for state abbreviations
 

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

Formatting cell for state abbreviations
 

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

Formatting cell for state abbreviations
 

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