VBA Adding Code to Chart Sheet via Macro

[mrquitzal]

Hello, so I am using an event macro to pull (x,y) points off of charts in their own sheet which looks like the following:

Option Explicit
Private Sub Chart_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
        ByVal x As Long, ByVal y As Long)
 
    Dim ElementID As Long, Arg1 As Long, Arg2 As Long
    Dim myX As Variant, myY As Double
    Dim WS As Worksheet
    Dim rowNum As Long
    With ActiveChart
        ' Pass x & y, return ElementID and Args
        .GetChartElement x, y, ElementID, Arg1, Arg2
 
        ' Did we click over a point or data label?
        If ElementID = xlSeries Or ElementID = xlDataLabel Then
            If Arg2 > 0 Then
                ' Extract x value from array of x values
                myX = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).XValues, Arg2)
                ' Extract y value from array of y values
                myY = WorksheetFunction.Index _
                    (.SeriesCollection(Arg1).Values, Arg2)
 
                ' Determine which boxes to fill
                If Shift = 1 Then
                    
                    Set WS = ActiveWorkbook.Sheets("Peaks")
                    
                                        
                    rowNum = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
                    
                                                            
                    If IsEmpty(WS.Range("C" & rowNum).Value) = True Then
                        
                        WS.Cells(rowNum, 3).Value = myX
                        WS.Cells(rowNum, 4).Value = myY
                    
                    Else
                        
                        WS.Cells(rowNum + 1, 1).Value = myX
                        WS.Cells(rowNum + 1, 2).Value = myY
                    
                    End If
                    
                End If
                
                If Shift = 4 Then
                    
                    Set WS = ActiveWorkbook.Sheets("StaSti")
                    
                                         
                    rowNum = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
                    
                    If IsEmpty(WS.Cells(rowNum, 2).Value) = True Then
                    
                        WS.Cells(rowNum, 2).Value = myX
                                            
                    Else
                        
                        WS.Cells(rowNum + 1, 1).Value = myX
                                            
                    End If
                    
                End If
                    
            End If
        End If
    End With
End Sub

This works perfectly fine when I paste it into the code for the Chart. I am doing this for multiple charts so I have the following code which moves a chart from where it was generated to the chart sheet:

Option Explicit
Public Sub MoveChart()
 
Dim wk As Worksheet

Set wk = Worksheets("Sheet4")


Application.DisplayAlerts = False
Charts("Chart1").Delete
Application.DisplayAlerts = True

wk.ChartObjects(1).Chart.Location xlLocationAsNewSheet, "Chart1"
Charts(1).Name = "Chart1"
'ActiveWorkbook.VBProject.VBComponents.Item("Chart1").CodeModule.AddFromFile (ActiveWorkbook.Path & "point_save.cls")
          
        

End Sub

As can be seen in the commented line, I am trying to get the first code (exported as point_save.cls) pasted into the newly made chart sheet so that I can keep grabbing the data points.

How can I get the macro to correctly initialize a chart on its own sheet with the first code?

Additional:

I originally tired having a chart sheet with the code already in it on a separate workbook and copying that chart to the active workbook then Chart.Location xlLocationAsObject to just move the chart, but the first code wouldn't run. If I manually pasted the code into the chart sheet it would then work fine. So any solution that gets my chart off "Sheet4" into a chart "Chart1" that is its own sheet and allows the event macro to run is good enough.


Thanks for any help.

 

[Domenic]

When referring to the chart sheet via VBComponents.Item, you'll need to use the code name for the sheet, not the sheet name. Also, it looks like you're missing a backslash (\) in the path and filename for your .cls file. Also, probably a good idea to add error handling in case the chart sheet doesn't already exist when trying to delete it. Try...

Option Explicit

Public Sub MoveChart()
 
    Dim wksSource As Worksheet
    Dim objChart As Chart
    Dim strChartName As String
    
    strChartName = "Chart1"
    
    Set wksSource = Worksheets("Sheet4")
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Charts(strChartName).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set objChart = wksSource.ChartObjects(1).Chart.Location(xlLocationAsNewSheet, strChartName)
    
    objChart.Name = strChartName
    
    ActiveWorkbook.VBProject.VBComponents.Item(objChart.CodeName).CodeModule.AddFromFile ActiveWorkbook.Path & "\point_save.cls"
              
End Sub

Hope this helps!

 

[mrquitzal]

That works beautifully Domenic, many thanks.