Hello Excel users and experts,

For the life of me I cannot get this simple change event to fire. I select
A2, make a change in it and hit Enter... nothing! I've tried it in the
sheet module, the ThisWorkbook module and a regular module. Still no go. I
saved and closed Excel, re-opened still no go.

This is fairly simple stuff and I have a few other change event macros in
other workbooks that work just fine for me. If I assign LastNameEnter to a
button it works fine. I have two other macros I want to be called with this
change event, but can't even get one to work. I Google searched and found
an example by Bernie D. to do this. His was without the EnableEvents and he
used Call in front of the macro name. I've tried that too.
I have also tried "If Target = Range("A2") then" ...

I'm probably looking right past some dumb oversight. Any ideas???

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$A$2" Then
LastNameEnter
End If

Application.EnableEvents = True
End Sub

Sub LastNameEnter()
Range("A2").Select
Range("LNDest").ClearContents

Selection.TextToColumns Destination:= _
ActiveCell.Offset(4, 0), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1))

Range("LastName", "A2").ClearContents
Range("B2").Select
End Sub

Thanks for any help.
Regards,
Howard

I believe the Application.EnableEvents = False is the culprit.
--
Pops Jackson


"L. Howard Kittle" wrote:

Hello Excel users and experts,

For the life of me I cannot get this simple change event to fire. I select
A2, make a change in it and hit Enter... nothing! I've tried it in the
sheet module, the ThisWorkbook module and a regular module. Still no go. I
saved and closed Excel, re-opened still no go.

This is fairly simple stuff and I have a few other change event macros in
other workbooks that work just fine for me. If I assign LastNameEnter to a
button it works fine. I have two other macros I want to be called with this
change event, but can't even get one to work. I Google searched and found
an example by Bernie D. to do this. His was without the EnableEvents and he
used Call in front of the macro name. I've tried that too.
I have also tried "If Target = Range("A2") then" ...

I'm probably looking right past some dumb oversight. Any ideas???

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$A$2" Then
LastNameEnter
End If

Application.EnableEvents = True
End Sub

Sub LastNameEnter()
Range("A2").Select
Range("LNDest").ClearContents

Selection.TextToColumns Destination:= _
ActiveCell.Offset(4, 0), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1))

Range("LastName", "A2").ClearContents
Range("B2").Select
End Sub

Thanks for any help.
Regards,
Howard

Are you putting the code behind the worksheet that should have this behavior?
It doesn't belong in ThisWorkbook.

Did you enable macros when you opened the workbook?

Is your security level set to allow macros--even after a prompt?

Did you test this and have it fail--and have .enableevents in the False
position?

Get to the VBE, hit ctrl-g to see the immediate window and type this
application.enableevents = true
and hit enter



"L. Howard Kittle" wrote:

Hello Excel users and experts,

For the life of me I cannot get this simple change event to fire. I select
A2, make a change in it and hit Enter... nothing! I've tried it in the
sheet module, the ThisWorkbook module and a regular module. Still no go. I
saved and closed Excel, re-opened still no go.

This is fairly simple stuff and I have a few other change event macros in
other workbooks that work just fine for me. If I assign LastNameEnter to a
button it works fine. I have two other macros I want to be called with this
change event, but can't even get one to work. I Google searched and found
an example by Bernie D. to do this. His was without the EnableEvents and he
used Call in front of the macro name. I've tried that too.
I have also tried "If Target = Range("A2") then" ...

I'm probably looking right past some dumb oversight. Any ideas???

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$A$2" Then
LastNameEnter
End If

Application.EnableEvents = True
End Sub

Sub LastNameEnter()
Range("A2").Select
Range("LNDest").ClearContents

Selection.TextToColumns Destination:= _
ActiveCell.Offset(4, 0), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1))

Range("LastName", "A2").ClearContents
Range("B2").Select
End Sub

Thanks for any help.
Regards,
Howard

--

Dave Peterson

Hi Dave & Pops,

Did you test this and have it fail--and have .enableevents in the False
position?

Yes!

Get to the VBE, hit ctrl-g to see the immediate window and type this
application.enableevents = true
and hit enter

This was the cure.

Thanks for the help. Never had that happen before. I had some goffy code
in the macros that were being cqlled and change event would go into a loop.
I thought enableevents false would stop the loop. (sure did, but in a bad
way)

Thanks again, guys.
Regards,
Howard

"Dave Peterson" wrote in message
...
Are you putting the code behind the worksheet that should have this
behavior?
It doesn't belong in ThisWorkbook.

Did you enable macros when you opened the workbook?

Is your security level set to allow macros--even after a prompt?

Did you test this and have it fail--and have .enableevents in the False
position?

Get to the VBE, hit ctrl-g to see the immediate window and type this
application.enableevents = true
and hit enter



"L. Howard Kittle" wrote:

Hello Excel users and experts,

For the life of me I cannot get this simple change event to fire. I
select
A2, make a change in it and hit Enter... nothing! I've tried it in the
sheet module, the ThisWorkbook module and a regular module. Still no go.
I
saved and closed Excel, re-opened still no go.

This is fairly simple stuff and I have a few other change event macros in
other workbooks that work just fine for me. If I assign LastNameEnter to
a
button it works fine. I have two other macros I want to be called with
this
change event, but can't even get one to work. I Google searched and
found
an example by Bernie D. to do this. His was without the EnableEvents and
he
used Call in front of the macro name. I've tried that too.
I have also tried "If Target = Range("A2") then" ...

I'm probably looking right past some dumb oversight. Any ideas???

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target.Address = "$A$2" Then
LastNameEnter
End If

Application.EnableEvents = True
End Sub

Sub LastNameEnter()
Range("A2").Select
Range("LNDest").ClearContents

Selection.TextToColumns Destination:= _
ActiveCell.Offset(4, 0), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1), Array(9, 1))

Range("LastName", "A2").ClearContents
Range("B2").Select
End Sub

Thanks for any help.
Regards,
Howard

--

Dave Peterson