L. Howard Kittle
12/16/2006 5:39:00 AM
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" <petersod@verizonXSPAM.net> wrote in message
news:45834A54.6B63D354@verizonXSPAM.net...
> 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