Wasn't sure if I had correctly unwrapped your code but here's my take on
what I think you are trying to do.

Sub test()
' assumes a Chart sheet is active that's NOT in thisworkbook
CreateEventProcedure3 ActiveSheet.Name, "My Custom Chart Type"
End Sub

Sub CreateEventProcedure3(Sheetname As String, sStyle As String)
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long, i As Long
Dim s As String ' ?
Dim sCodeName As String
Dim arrLines(0 To 1) As String
Const DQUOTE = """" ' one " character

arrLines(0) = " ActiveChart.ApplyCustomType ChartType:=xlUserDefined, _"
arrLines(1) = " TypeName:=" & DQUOTE & sStyle & DQUOTE

sCodeName = ActiveWorkbook.Charts(Sheetname).CodeName
If Len(sCodeName) = 0 Then
' flash the VBIDE to update codename of a new sheet
With Application.VBE.MainWindow
.Visible = True
.Visible = False
End With
sCodeName = ActiveWorkbook.Charts(Sheetname).CodeName
End If

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

' don't step through this bit, press F5 if necessary
With CodeMod
LineNum = .CreateEventProc("Calculate", "Chart")
For i = 0 To 1
LineNum = LineNum + 1
.InsertLines LineNum, arrLines(i)
Next
End With

End Sub


A few comments:

- Suggest don't re-use Keywords like 'CodeName' & 'Style' as variable names
- Don't step through the code where indicated
- Never attempt to write code to object modules in the same workbook that's
doing the writing, ie don't write to 'own' object modules.
- you only need to use tricks to update the codename if needed (flashing the
VBE is one way), which is when the VBE is closed and a new sheet is added
since the last save/close/reopen.

- And finally, surely you don't want to update the style of the chart every
time it calculates !

For a newbie quite ambitious :-)

Regards,
Peter T

"Thomas" wrote in message
...
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