CAN ANYONE SOVLE THIS PROBLEM!!!

[Guest]

HERE'S THE CODE:
Dim NDXcode As String, StartLine As Long
NDXcode = ActiveSheet.CodeName
With ActiveWorkbook.VBProject.VBComponents(NDXcode).CodeModule
StartLine = .CreateEventProc("SelectionChange", "Worksheet")
.InsertLines StartLine, _
<TEXT IS INSERTED HERE>
HERE'S THE ERROR:
" the object invoked has disconnected from its client" and Excel crashes
THE ERROR OCCURS AT .InsertLine StartLine,...BUT IT ALSO HAPPENS IF I SINGLE
STEP TO THE PREVIOUS LINE AND TYPE IN THE IMMEDIATE WINDOW "Print StartLine"

 

[Myrna Larson]

What happens if you turn off Event trapping before you try to insert the
procedure?

 

[Guest]

I haven't tried that. But I will and let you know.

 

[Guest]

How do you turn off Event trapping?

I haven't tried that. But I will and let you know.

 

[Tom Ogilvy]

I think she mean's

Application.EnableEvents = False

 

[Guest]

Thanks Tom. I tried that and it didn't make any difference. Do you have any
ideas?

Jim

 

[Tom Ogilvy]

If I did, I wouldn't make you beg for them, I would have posted them.
Sorry.

 

[Tom Ogilvy]

If you just want a work around and then get on with it.
I think if you use the normal way to create a sub in a module and not use
CreateEventProc you might be more successful. I rarely played with
CreateEventProc, but I don't recall every being successful with it (mostly
in xl97).

http://www.cpearson.com/excel/vbe.htm

if you want to see sample code on just writing code in a module (although
Chip has examples of CreateEventProc there also as I recall).

 

[Tushar Mehta]

Obviously, you know your app better than I do but in the 20+ years I've
been developing systems I have never come across a case where I had to
code a program to add code to another file -- well, other than when
writing a compiler or a system generator {g}.

IMO, propagating code through workbooks is a sure-fire way to create a
maintenance, security, and integrity nightmare. Doing so through code
simply compounds the problem.

If you want to handle worksheet events, use an application level event
handler within your own program. See the XL VBA help topic 'Using
Events with the Application Object' for more.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions