I have an Excel-addin which I must upgrade. To keep compatibility with
previous version of the add-in I need to change the Workbook macros
previously created by this add-in. My intention was to use the possibilities
CodeModule offers.

The following code crashes when I run it towards an already created Workbook
with Workbook macros and creates a new event routine. In previous Workbook I
hade the routines

Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_Close()

I now wanted to add Workbook_SheetDeactivate as well with the following code
(I´ve taken the essential parts)

..
..
With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

' Check if it exists, if not create it
GetEventProcFrame "SheetDeactivate", "Workbook", startLine, endLine
' Fill in the body of the macro
lastLine = InsertWorkbook_SheetDeactivate(startLine)
..
..

'
'
'
Private Sub GetEventProcFrame(argEvent As String, argObject As String,
startLine As Long, endLine As Long)

With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

If Not .Find("Sub " & argObject & "_" & argEvent, 1, 1, -1, -1) Then
.CreateEventProc argEvent, argObject
End If

startLine = .ProcStartLine(argObject & "_" & argEvent, vbext_pk_Proc)
endLine = startLine + .ProcCountLines(argObject & "_" & argEvent,
vbext_pk_Proc) - 1
startLine = .ProcBodyLine(argObject & "_" & argEvent, vbext_pk_Proc)

If startLine 0 And endLine startLine Then
.DeleteLines startLine + 1, endLine - startLine - 1
Else
MsgBox "Macro " & argObject & "_" & argEvent & " could not be inserted"
End If

End With

End Sub


A notable thing is that if I run the code on a not yet saved Workbook it
works !

Bertil,

do you set
application.enableevents = False
before changing the eventhandler procedures?

just a thought but it might help ..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bertil Emmertz wrote :

I have an Excel-addin which I must upgrade. To keep compatibility
with previous version of the add-in I need to change the Workbook
macros previously created by this add-in. My intention was to use the
possibilities CodeModule offers.

The following code crashes when I run it towards an already created
Workbook with Workbook macros and creates a new event routine. In
previous Workbook I hade the routines

Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_Close()

I now wanted to add Workbook_SheetDeactivate as well with the
following code (I´ve taken the essential parts)

.
.
With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

' Check if it exists, if not create it
GetEventProcFrame "SheetDeactivate", "Workbook", startLine, endLine
' Fill in the body of the macro
lastLine = InsertWorkbook_SheetDeactivate(startLine)
.
.

'
'
'
Private Sub GetEventProcFrame(argEvent As String, argObject As
String, startLine As Long, endLine As Long)

With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

If Not .Find("Sub " & argObject & "_" & argEvent, 1, 1, -1, -1)
Then .CreateEventProc argEvent, argObject
End If

startLine = .ProcStartLine(argObject & "_" & argEvent,
vbext_pk_Proc) endLine = startLine + .ProcCountLines(argObject &
"_" & argEvent, vbext_pk_Proc) - 1
startLine = .ProcBodyLine(argObject & "_" & argEvent,
vbext_pk_Proc)
If startLine 0 And endLine startLine Then
.DeleteLines startLine + 1, endLine - startLine - 1
Else
MsgBox "Macro " & argObject & "_" & argEvent & " could not be
inserted" End If

End With

End Sub


A notable thing is that if I run the code on a not yet saved Workbook
it works !

Yes I have disabled events. I even tried to disable my Macafee virus scan but
without any success. Any help in this matter is appreciated, work-arounds etc.

"keepITcool" skrev:

Bertil,

do you set
application.enableevents = False
before changing the eventhandler procedures?

just a thought but it might help ..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bertil Emmertz wrote :

I have an Excel-addin which I must upgrade. To keep compatibility
with previous version of the add-in I need to change the Workbook
macros previously created by this add-in. My intention was to use the
possibilities CodeModule offers.

The following code crashes when I run it towards an already created
Workbook with Workbook macros and creates a new event routine. In
previous Workbook I hade the routines

Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_Close()

I now wanted to add Workbook_SheetDeactivate as well with the
following code (I´ve taken the essential parts)

.
.
With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

' Check if it exists, if not create it
GetEventProcFrame "SheetDeactivate", "Workbook", startLine, endLine
' Fill in the body of the macro
lastLine = InsertWorkbook_SheetDeactivate(startLine)
.
.

'
'
'
Private Sub GetEventProcFrame(argEvent As String, argObject As
String, startLine As Long, endLine As Long)

With WB.VBProject.VBComponents("ThisWorkbook").CodeModu le

If Not .Find("Sub " & argObject & "_" & argEvent, 1, 1, -1, -1)
Then .CreateEventProc argEvent, argObject
End If

startLine = .ProcStartLine(argObject & "_" & argEvent,
vbext_pk_Proc) endLine = startLine + .ProcCountLines(argObject &
"_" & argEvent, vbext_pk_Proc) - 1
startLine = .ProcBodyLine(argObject & "_" & argEvent,
vbext_pk_Proc)
If startLine 0 And endLine startLine Then
.DeleteLines startLine + 1, endLine - startLine - 1
Else
MsgBox "Macro " & argObject & "_" & argEvent & " could not be
inserted" End If

End With

End Sub


A notable thing is that if I run the code on a not yet saved Workbook
it works !