Using CreateEventProc fails when used with a Worksheet.
 

Excel 2000 on WinNT platform.

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

Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbo ok.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???

Using CreateEventProc fails when used with a Worksheet.
 

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) wrote:

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

Using CreateEventProc fails when used with a Worksheet.
 

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!

keepitcool wrote in message .. .
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) wrote:

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

Using CreateEventProc fails when used with a Worksheet.
 

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...)



(George Doster) wrote in message . com...
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!

keepitcool wrote in message .. .
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) wrote:

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