How to copy code from XLAM into new workbook

[Rasm]

I have a Excel Add-in (XLAM file) - this allows me to chart data on any workbook of the type XLSX - however I use a class modules to trap chart events - so when a user clicks on a datapoint in the chart - I want the events to fire - but it require the code below to be embeeded into the sheet in the XLSX workbook - any idea how I from my XLAM code can write that code to the worksheet where the chart resides. All my datasheets used for making the charts are standard XLSX files.

Dim MyChart As New Class1
Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub

 

[AlphaFrog]

Perhaps this will help...

How to create application-level event handlers in Excel
http://support.microsoft.com/kb/213566


Or this...

Chart Events in Microsoft Excel
http://www.computorcompanion.com/LPMArticle.asp?ID=221
(see the Using Event Procedures to Enable Chart Events section about 3/4 of the page down)

 

[Rasm]

Thanks as always AlphaFrog - the 2nd article I have already used - but I will study the MS one - I have got my event handler for the charts working in my test code - only problem is when the code is in the XLAM and not the activeworkbook - I kind suspect that the event handler in thisworkbook in the XLAM does not trigger - unless I run the code with full compile. Let me play with this.

 

[AlphaFrog]

This is definitely out of my league, but I got this to work in some fashion....

Create a new class module called ClsAppEvents and put this in it.

Public WithEvents appevent As Application
Dim MyChart As New Class1

Private Sub appevent_SheetActivate(ByVal Sh As Object)
    If Sh.ChartObjects.Count > 0 Then
        Set MyChart = Sh.ChartObjects(1).Chart
    End If
End Sub

Put this in your XLAM Thisworkbook module to instantiate the ClsAppEvents application-level event handlers when the Add-in is opened.

Public myobject As New ClsAppEvents

Private Sub Workbook_Open()
    Set myobject.appevent = Application
End Sub

 

[Rasm]

I got it semi working - hehehehe - but it scare me when you say out of your expertise - But I am giving it a shot - thanks for more input.

 

[Rasm]

This is my class module code - got it from Andrew P - But I will def try what you suggest in a bit. But this allows a nice way of adding comments to specif points on the chart

Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name & " point " & b & " (" & Format(.DataLabel.Text, "####0.##") & ")"
             With .DataLabel
                .Text = Txt
                If xlLabelPositionAbove > 0 Then .Position = xlLabelPositionAbove
                .Font.Size = 8
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub
Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
   
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

 

[Rasm]

This my code in ThisWorkBook - in the XLAM - So looks very similar to what you send me.


FYI - I keep all my settings in a worksheet within the XLAM - so when I close it - I also save it - this way all my settings can be used when I load up. I suppose I should do this in the registry - But this actually works very nicely.

Option Explicit
Option Compare Text
Dim MyChart As New Class1
Dim NumCharts As Long
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    [COLOR=red]ThisWorkbook.Save
[/COLOR]    CleanUp
    'On Error Resume Next  'in case workbook opened read-only
End Sub
Private Sub Workbook_Open()       Dim ctrl As CommandBarControl
    Set ctrl = Application.CommandBars.FindControl(Tag:="NameOfMyApps")
    If Not ctrl Is Nothing Then CleanUp
      Create_Menu
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    NumCharts = ActiveSheet.ChartObjects.Count
    If NumCharts >= 1 Then
        Set MyChart.Ch = Nothing
        Set MyChart.Ch = ActiveSheet.ChartObjects(NumCharts).Chart
    End If
    Err.Clear
 End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    NumCharts = ActiveSheet.ChartObjects.Count
    If NumCharts >= 1 Then
        Set MyChart.Ch = Nothing
        Set MyChart.Ch = ActiveSheet.ChartObjects(NumCharts).Chart
    End If
    Err.Clear
End Sub