Formatting cell for state abbreviations

  • Thread starter Thread starter LovesArt247
  • Start date Start date
L

LovesArt247

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
 
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
 
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.
 
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.
 
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
 
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?
 
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?
Click to expand...

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
 
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
 
Good idea.

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


Gord
 
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
 
Good idea.

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

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
 
Back
Top