Hi all,

I am quite a newbie to VBA, but with the help of Chip Pearson's
excellent site (thank you!!!) and a lot of trial and error I figured
out how to get where I want.
However, when it comes to event handling, I am stuck and so I hope
someone here finds the mistake in my code.

I'm trying to create a macro that adds event procedures to a workbook.
The workbook consists of several pivot charts. I want every pivot
chart to change its chart type every time the chart is recalculated.

Here's the code I want to be inserted into my chart's modules as a
result of my macro:

Private Sub Chart_Calculate()
ActiveChart.ApplyCustomType ChartType:=xlUserDefined,
TypeName:="My Custom Chart Type"
End Sub


The macro itself:

Sub CreateEventProcedure3(Sheetname As String, Style As String)

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Dim s As String
Dim CodeName As String

s = ActiveWorkbook.VBProject.name 'this is done to prevent empty
codename, learned from

CodeName = Charts(Sheetname).CodeName

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(CodeName)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Calculate", "Chart")
LineNum = LineNum + 1
.InsertLines LineNum, " ActiveChart.ApplyCustomType
ChartType:=xlUserDefined, TypeName:= " & DQUOTE & Style & DQUOTE
.VBE.MainWindow.Visible = False
End With
End Sub



Sub Eventmaker()

Application.ScreenUpdating = False
Application.VBE.MainWindow.Visible = False

CreateEventProcedure3 "Chart1", "Type 1"
CreateEventProcedure3 "Chart2", "Type 2"

Application.ScreenUpdating = True
Application.VBE.MainWindow.Visible = False

End Sub

The first call of CreateEventProcedure3 usually works fine, but
afterwards excel crashes. I did not find the exact position where
Excel goes down as it seems to crash kind of delayed.
I mention the pivot charts because when I modify my code to create
events on ordinary work sheets, everything works fine. I' running
Excel 2003 on Win XP.

Did I forget to initialize something? Am I accessing memory in a way I
shouldn't?

Any help is highly appreciated!

Thanks in advance
Thomas