How to add Chart_Activate code using VBA
 

Hi,
I have a macro that creates a workbook with some pivot charts.
I want to have the macro write code to call a procedure whenever the pivot
chart is activated but it doesn't seem to work.

All I really want to do is add the following code in the Chart - Activate
Event Procedu
" Call Format_Pivot_Chart"
("Format_Pivot_Chart" is a routine already in the workbook.)

I've used the code below to successfully add code to the Thisworkbook.Open
See partial code below the routine to see what's really different.

Thanks for any help,
MikeZz


Public Sub A85_Transfer_Macros_Chart_Activate(shtChart As Worksheet)
Const sStr As String = "C:\Temp\CodeChartActivate" & shtChart & ".txt"

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Dim FileNum
Dim resultstr

Application.EnableEvents = False

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = destWb.VBProject

Set VBComp = VBProj.VBComponents(shtChart.CodeName)
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CreateEventProc("Activate", "Chart")
LineNum = LineNum + 1
.InsertLines LineNum, " Call Format_Pivot_Chart"
End With

Set CodeMod = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
Set VBAEditor = Nothing

Application.EnableEvents = True

End Sub


PARTIAL CODE USED TO WRITE TO Thisworkbook.Open

Set VBProj = destWb.VBProject

Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Open", "Workbook")
LineNum = LineNum + 1
.InsertLines LineNum, " Call OpenForm"
End With

How to add Chart_Activate code using VBA
 

I'd say it's easier to put the desired code into a chart events class
module, and create an instance of the class for each chart. This might get
you started:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"MikeZz" wrote in message
...
Hi,
I have a macro that creates a workbook with some pivot charts.
I want to have the macro write code to call a procedure whenever the pivot
chart is activated but it doesn't seem to work.

All I really want to do is add the following code in the Chart - Activate
Event Procedu
" Call Format_Pivot_Chart"
("Format_Pivot_Chart" is a routine already in the workbook.)

I've used the code below to successfully add code to the Thisworkbook.Open
See partial code below the routine to see what's really different.

Thanks for any help,
MikeZz


Public Sub A85_Transfer_Macros_Chart_Activate(shtChart As Worksheet)
Const sStr As String = "C:\Temp\CodeChartActivate" & shtChart & ".txt"

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Dim FileNum
Dim resultstr

Application.EnableEvents = False

Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = destWb.VBProject

Set VBComp = VBProj.VBComponents(shtChart.CodeName)
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CreateEventProc("Activate", "Chart")
LineNum = LineNum + 1
.InsertLines LineNum, " Call Format_Pivot_Chart"
End With

Set CodeMod = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
Set VBAEditor = Nothing

Application.EnableEvents = True

End Sub


PARTIAL CODE USED TO WRITE TO Thisworkbook.Open

Set VBProj = destWb.VBProject

Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Open", "Workbook")
LineNum = LineNum + 1
.InsertLines LineNum, " Call OpenForm"
End With