Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert State abbreviations color coded cell | Excel Worksheet Functions | |||
state abbreviations | New Users to Excel | |||
Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
create a list box in one cell of state abbreviations | Excel Worksheet Functions | |||
US State Abbreviations List | New Users to Excel |