I am trying to get a macro to work on a chart in a worksheet using the Chart.BeforeDoubleClick event.
I have been able to use BeforeDoubleClick event (without the use of class modules) in a stand-alone chart. But I would like to get it to work in a chart embedded as object in a worksheet. The idea is to then replicate this for multiple charts within a single worksheet.
Following the advice in this book (pp. 172-3), I have done the following:
Created new workbook. In the worksheet Sheet1 added 2 columns of random data and added a scatterplot of it in the same sheet next to the data.
Inserted a class module named "cl_ChartEvents" with code:
Public WithEvents myChartClass As Chart
Created a standard module with code:
Dim myClassModule As New cl_ChartEvents Sub InitializeChart() Set myClassModule.myChartClass = _ Worksheets("Sheet1").ChartObjects(1).Chart End Sub
In the VBA editor, double-clicked on "Sheet1 (Sheet1) object and inserted code:
Private Sub MyChartClass_BeforeDoubleClick(ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Select Case ElementID Case xlLegend Me.HasLegend = False Cancel = True Case xlAxis Me.HasLegend = True Cancel = True End Select End Sub
Click Run and run the InitializeChart macro.
When I then double-click the legend on the chart, nothing happens and Excel simply opens the Format Legend properties box as usual.
I have done my homework online looking at forums etc. but was not able to find any other hint at how the BeforeDoubleClick event could be implemented to work for a chart within a regular worksheet. The approach in the book seems to indicate this is doable though.
Any help would be appreciated! Getting this to work would help lots of others with similar questions I have seen on the web. Thank you.