Re: Formatting cell for state abbreviations

[Bernard Liengme]

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" <Loves...@yahoo.com> wrote in message
news:DBDC988A-753D-4E60...@microsoft.com...
>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

[Hile]

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

[Gord Dibben]

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 <Hi...@discussions.microsoft.com>
wrote:

[Hile]

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

[Ron Rosenfeld]

On Tue, 14 Apr 2009 11:23:01 -0700, Hile <Hi...@discussions.microsoft.com>
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

[Gord Dibben]

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 <Hi...@discussions.microsoft.com>
wrote:

>Thank you so much. Can I use a range of columns if I want to apply this

[Gord Dibben]

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 <ronros...@nospam.org>
wrote:

[Dave Peterson]

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

--

Dave Peterson

[Ron Rosenfeld]

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