CreateEventProc causes Excel to crash

[murad5]

Hi,

I'm trying to add a SheetBeforeDoubleClick event procedure to the ThisWorkbook module programatically, but when I try to call the CreateEventProc method, Excel crashes immediately. I have no idea what is causing this!

Here's my code:

Set vbCodeMod = wkbTemp.VBProject.VBComponents("ThisWorkbook").CodeModule
With vbCodeMod
' It crashes on the next line
intStart = .CreateEventProc("SheetBeforeDoubleClick", "Workbook") + 1
.InsertLines intStart, "If Not(Double_Click(sh, target, cancel)) Then MsgBox(""Error"")"
End With


All ideas gratefully received!

 

[murad5]

If anyone has any thoughts on this one I'd really appreciate hearing them...

 

[SkipVought]

Hi,

How about this?

Private Sub Create_Selection_Change_Event()
   Dim sStr
   sStr = sStr & "Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbCrLf
   sStr = sStr & "  If Not(Double_Click(sh, target, cancel)) Then MsgBox(""Error"")" & vbCrLf
   sStr = sStr & "End Sub"
   'Use AddFromString to manually add the sub automatically, manually speaking!
   ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.AddFromString sStr
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[murad5]

Hi Skip,

Thanks for your help. I tried that method earlier, but it still crashes my system. I have a suspicion that the problem is the reference to the Visual Basic Extensibility library. The workbook with the code in it has this reference, but the workbook I'm trying to add the code to doesn't. Is there any way to set a reference to a type library through VB?

Thanks,

Richard

 

[murad5]

It seems the issue was caused by trying to add an event procedure in a different workbook to the one which contained the code which was running.

To get around this, I created a procedure to create the event procedure in the new workbook and called it Autpen, therefore the next time it is opened the event procedure will run. I placed a check in the code so that it will only run when it does not find the event procedure present, so it should run only once.