CAN ANYONE SOVLE THIS PROBLEM!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

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"
 
What happens if you turn off Event trapping before you try to insert the
procedure?
 
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).
 
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
 
Back
Top