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