Using CreateEventProc fails when used with a Worksheet.

[George Doster]

Excel 2000 on WinNT platform.

I'm trying to insert some code into a specific worksheet in a
workbook. The code is shown here:

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Worksheets("Images").CodeName).CodeModule
StartLine = VBCodeMod.CreateEventProc("Change", "Worksheet") + 1
VBCodeMod.InsertLines StartLine, "Msgbox ""Hello World"",vbOkOnly"

(sorry about the line break...)

Once the code executes the CreateEventProc line I get an error box
that says:

Run-time error '-2147417848(80010108):'
Automation error
The object invoked has disconnected from its clients.

Then Excel is hosed... have to quit and reopen.

The interesting part is if I try to insert some code into ThisWorkbook
everything works just fine. I've alse tried just using InsertLines
without a CreateEventProc call. Same effect. Any ideas???

 

[keepitcool]

yep..

crashed my excel too.. thx!

But after i scratched my head..
and thought WHY it crashed ..

application.enableEvents=false
your code
application.enableEvents=true

solved it



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[George Doster]

Oh crap... I tried your idea. Looked like a great idea to me.. it even
made sense. But... alas... I still crash with the same message. I
wonder what else is going on? Anyone else got an idea? I'm gonna try
this 'outside' the whole project and make sure it's not some else
going on in the code.

Thanks!

 

[George Doster]

Well... it appears the problem only occurs in a particular workbook. I
can use the code to put event code into other workbooks, just not the
one I want. Anyone have any problem like this. Is there some no no
about inserting code into a worksheet that has certain things on it?
(I dunno... bmp images, certain calculations? anything...)

 

[bxy]

1/ use insertlines
2/ need remove private
instead "private sub ___event___" simple "sub ___event___'