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

okay so what happened when you put a breakpoint on the first line of your
code and changed a state & then stepped through the code, where it did
"fail" ... give me that line and the lines above and we might be able to
figure out a solution ...

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"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

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

Hi

when you choose the CHANGE event from the procedure drop down in the VBE
window it should have created it like this

Private Sub Worksheet_Change(ByVal Target As Range)

and your code should then reference the TARGET object not the activecell
e.g.

Private Sub Worksheet_Change(ByVal Target As Range)

IF Intersect(Target,Me.Range("A1:A100")) is Nothing Then Exit Sub

SELECT CASE Target.value
Case "Alabama"
Target.value = "AL"
Case = "Alaska"
Target.Value = "AK"
'repeat structure for all 50 states then
END SELECT

End sub
------

change the A1:A100 to cover the range that your drop down boxes are in


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"D" wrote in message
...
Ok.
I don't know where it failed because I didn't get an error that it did
fail
but here is the first lines of code for the states. When I put that text
in
a cell nothing happens its just the word for example if I put in Alabama
in a
cell it won't change to AL it just stays Alabama.
Of course at the bottom there is an End If and an End Sub. So here it is.

Private Sub Worksheet_Change()

If ActiveCell.Value = "Alabama" Then
ActiveCell.Value = "AL"
ElseIf ActiveCell.Value = "Alaska" Then
ActiveCell.Value = "AK"
ElseIf ActiveCell.Value = "Arizona" Then
ActiveCell.Value = "AZ"
ElseIf ActiveCell.Value = "Arkansas" Then
ActiveCell.Value = "AK"
ElseIf ActiveCell.Value = "California" Then
ActiveCell.Value = "CA"
ElseIf ActiveCell.Value = "Colorado" Then
ActiveCell.Value = "CO"
ElseIf ActiveCell.Value = "Connecticut" Then
ActiveCell.Value = "CT"
ElseIf ActiveCell.Value = "Delaware" Then
ActiveCell.Value = "DE"
And the list goes on.
Thanks for you help!
Let me know if you need any other information from the code but that is
basically all that I have.

"JulieD" wrote:

okay so what happened when you put a breakpoint on the first line of your
code and changed a state & then stepped through the code, where it did
"fail" ... give me that line and the lines above and we might be able to
figure out a solution ...

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"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

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