You shouldn't be running the macro. The macro, if placed as I have
instructed, would run automatically anytime there is an edit of a cell in
column 3. Yes, if your states entries will be in column 17, change the 3 to
17 (in fact the macro runs everytime there is an entry in any cell, but the
first check is if the triggering cell is column 3 (17) and if not, it quits.

If you don't use the protections I have suggested, then everytime State is
entered, your macro will run around 500 times as I recall, since your code
is changing a cell in the area where you want to react.

I altered my sample code to reflect matches to strings which are Proper Case


Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
if Target.column = 17 then
if Target.count 1 then exit sub
if len(Target) <= 2 then exit sub
Application.enableevents = False
Select Case strConv(Target.Value,vbProperCase)
Case "Alabama"
Target.Value = "AL"
Case "Alaska"
Target.Value = "AK"
Case "Arizona"
Target.Value = "AZ"
. . .
End Select
End if
ErrHandler:
Application.EnableEvents = True

End Sub


--
Regards,
Tom Ogilvy


"D" wrote in message
...
Ok. Cool. But some more questions. When I go to run the macro it asks me
for
a Macro Name and when I put a macro name and hit Create it goes to Module
2
with a Sub and the name. And when I go back to the Sheet1 with all the
code
and hit run again it asks me for another name. Also, I'm a bit lost on the
columns under the ErrHandler line. Do I put the column that the states are
in
which would be 17 where the 3 is at, and if so what do I put for the 2 and
1
that are in the code? Thanks so much for your help! Hopefully we can get
this
solved.

"Tom Ogilvy" wrote:

the
Private Sub Worksheet_Change
should be in the worksheet code module, not the thisworkbook code
module.

Right click on the sheet tab and select view code.

At the top of the resulting module,

In the left dropdown, select Worksheet
in the right dropdown, select Change

you should get

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

there also may be a SelectionChange there, but you can delete that.

Put your code the

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
if Target.column = 3 then
if Target.count 1 then exit sub
if len(Target) <= 2 then exit sub
Application.enableevents = False
Select Case Ucase(Target.Value)
Case "ALABAMA"
Target.Value = "AL"
. . .
End Select
End if
ErrHandler:
Application.EnableEvents = True

End Sub

The THISWORKBOOK module has an equivalent workbook level event:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

End Sub

But it doesn't sound like this is what you are using.

--
Regards,
Tom Ogilvy



"D" wrote in message
...
Well the security is not an issue because I have done macros before,
and
the
setting is set to "Low" because I don't want to deal with it. Anyways,
I
did
up an event in "ThisWorkbook" as a Private Sub Worksheet_Change and
then I
have the if and ifelse statements for the states and the
abbreviations. I
don't think you want the whole code because it is very long with all
50
states. I am not getting any errors its just that when I change the
cell
it
doesn't switch the text to the abbreviation. It doesn't do anything.
Hope
you
can help.
Thanks,
D

"JulieD" wrote:

Hi

if you'ld to post all your code then it might help figure out a
solution
.... things to check in the meantime
tools / macro / macros - security set to medium or less and you
choose
enable macros on open
in the immediate window type (VBE Window / view / immediate window)
application.enableevents = true
and press enter


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"D" wrote in message
...
Hello.
I have a cell list of states, from data validation, that a user
created
and
now would like when a user types the state or selects the state
from a
drop-down list, it converts it to the abbreviation of the state. I
have an
If
ActiveCell.Value = "Alabama" Then
ActiveCell.Value = "AL"
but it doesn't seem to be working. ??? Don't know why? I thought
this
would
be easy but not sure now. Well hope you can help.
Thanks,
--
D