VBA Adding Code to Chart Sheet via Macro

mrquitzal

New Member
Joined
Mar 22, 2019
Messages
2
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:

Code:
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:
Code:
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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...

Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top