Change Event not firing
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
|