Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CreateEventProc causes crash
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem in InsertLines and CreateEventProc | Excel Programming | |||
avoiding VBEopen when using CreateEventProc:AGAIN | Excel Programming | |||
avoiding VBEopen when using CreateEventProc | Excel Programming | |||
Excel crashes when using CreateEventProc towards CodeModule | Excel Programming | |||
Using CreateEventProc fails when used with a Worksheet. | Excel Programming |