1

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:

  1. 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.

  2. Inserted a class module named "cl_ChartEvents" with code:

    Public WithEvents myChartClass As Chart
    
  3. Created a standard module with code:

    Dim myClassModule As New cl_ChartEvents
    Sub InitializeChart()
    Set myClassModule.myChartClass = _ 
    Worksheets("Sheet1").ChartObjects(1).Chart
    End Sub
    
  4. 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  
    
  5. 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.

1
  • no takers yet..wondering if anyone has tried or has any ideas about this?
    – A.S
    Dec 17, 2014 at 15:01

1 Answer 1

2

Pretty sure your code in list item #4 needs to be in the Class Module for myChartClass and not in the Sheet1 code.

EDIT (to address the 'member not found' error): Revise the step 4 code to:

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.MyChartClass.HasLegend = False
        Cancel = True
    Case xlAxis
        Me.MyChartClass.HasLegend = True
        Cancel = True
    End Select
End Sub

The only change is the insertion of MyChartClass between Me and HasLegend in the two locations they occur.

Why this works: Me refers to the instance of the containing class module, cl_ChartEvents, which is not what has been bound to the chart of interest. The object MyChartClass is what is bound to the Chart. (A better name for MyChartClass would probably be MyChartObj, or something.) So, one has to drill down into Me.MyChartClass in order to manipulate the bound Chart.

EDIT 2 (to provide code to apply the custom event handling to every chart in a workbook): Replace the step 3 code with the following:

Dim ChartColl As New Collection

Sub LinkCharts()
    Dim workCls As cl_ChartEvents
    Dim ws As Worksheet
    Dim ch As Chart, chob As ChartObject

    ' Link all standalone charts
    For Each ch In ActiveWorkbook.Charts
        ' Must create a new instance of the class for each chart
        Set workCls = New cl_ChartEvents
        ' Link each chart to the myChartClass member of the new class instance
        Set workCls.myChartClass = ch
        ' Add the new instance of the class into the Collection object
        ChartColl.Add workCls
    Next ch

    ' Link all charts in objects in sheets
    For Each ws In ActiveWorkbook.Worksheets
        For Each chob In ws.ChartObjects
            Set workCls = New cl_ChartEvents
            Set workCls.myChartClass = chob.Chart
            ChartColl.Add workCls
        Next chob
    Next ws
End Sub

Sub UnlinkCharts()
    ' Removing the cl_ChartEvents instances from the Collection causes 
    '  causes them to be destroyed by garbage collection
    Do Until ChartColl.Count = 0
        ChartColl.Remove 1
    Loop
End Sub

If you only want certain charts in the workbook to be modified, the solution is more involved -- you would have to find some way to mark either the charts you do want modified, or the charts you don't want modified, and check each chart as you come across it for the mark. Quite doable, though.

4
  • Warmer! At least it seems to call the step 4 code when the chart is clicked. However it errors out on "Me.HasLegend = False" with a compile error saying "method or data member not found." Ideas?
    – A.S
    Dec 30, 2014 at 22:15
  • Thanks very much. The tweak in your answer works! The double-click chart functionality I actually need is a bit different from this example, but the approach seems on the right track. Will test with more elaborate macro and follow up. One remaining question is how to make this work for multiple charts/sheets. It works by simply adding another module and class module pair (substituting InitializeChart to InitializeChart2 with ChartObjects(2), and class module cl_ChartEvents2. Wondering if there might be a more elegant approach than replicating 2 modules for every chart?
    – A.S
    Dec 31, 2014 at 14:42
  • Edit 2 does it! Only one class module and one standard module are needed for the macro to work with all charts. Thanks a lot! Will also vote up once have enough rep.
    – A.S
    Dec 31, 2014 at 17:11
  • Hi @Brian, if you are interested there is another related question here: superuser.com/questions/859070/…
    – A.S
    Jan 2, 2015 at 12:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .