Chart Object Events

SimonP

New Member
Joined
Sep 25, 2002
Messages
48
Is it possible to capture chart object events for any chart object (either existing or new)? I want to be able to enable and disable custom commandbar buttons depending on whether a chart object is selected or not (the buttons run code that is specific to charts), similar to the way the Excel chart wizard button is only enabled when a chart is selected.
Capturing the events for a chart sheet is no problem, as is capturing the events for a specific named chart object, but I can't work out how to capture the events for any chart object.
Thanks
Simon
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need a class module to use events with an embedded chat.

Insert a Class Module and call it say ClassChart. Add the following line of code:

Code:
Public WithEvents ChartObj As Chart

Once you have done this you can choose ClassChart in the left dropdown and the events will be exposed in the right dropdown.

Before any event-handlers will run you will need to connect your chart object(s) with the object in the Class Module, like this (in a General Module):

Code:
Dim MyChart As New ClassChart
Set MyChart.ChartObj = Worksheets("Sheet1").ChartObjects(1).Chart
 

SimonP

New Member
Joined
Sep 25, 2002
Messages
48
Thanks for that - however I'm trying to create an add-in that will work on any chart, including new ones that users create, rather than an existing chart.
The bit I'm struggling with is linking the new class to ANY chart object, not just a specific named chart object. Can this be done?
Simon
 

SimonP

New Member
Joined
Sep 25, 2002
Messages
48
Thanks for that - however I'm trying to create an add-in that will work on any chart, including new ones that users create, rather than an existing chart.
The bit I'm struggling with is linking the new class to ANY chart object, not just a specific named chart object. Can this be done?
Simon
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could monitor Application level events, which also requires a Class module (see Help).

The WorkbookOpen event will fire when the user opens a workbook. You could then loop round the worksheets to find any charts and connect them with the Chart Class Object.

Code:
Dim MyCharts() As New ClassChart 

Sub EnableChartEvents()
    Dim Sh As WorkSheet
    Dim Ch As ChartObject
    Dim x As Integer
    x = 0
    On Error Resume Next
    For Each Sh in ActiveWorkbook.WorkSheets
        For Each Ch in Sh.ChartObjects
            x = x + 1
            Redim Preserve MyCharts(1 to x)
            Set MyCharts(x).ChartObj = Ch.Chart
        Next Ch
    Next Sh
End Sub

I can't find an event that fires if the user adds a chart, so I don't know how you would cater for that. Maybe SheetSelectionChange.
 

Forum statistics

Threads
1,144,290
Messages
5,723,518
Members
422,502
Latest member
barakgahtan

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