Following concerns ChartObject charts

However it does not produce an event when the chart is created ...

No it won't. As I mentioned originally the creation of a Chart does not
trigger an event, at least not one that's directly exposed to VBA.

Apart from sheet or workbook activate events in which to recreate or destroy
events, you could monitor say chartobjects.count in the cell select event

' in the normal module
Public gChtCnt as Long

' in GetCharts
gChtCnt = sht.ChartObjects.Count

' in thisworkbook or equivalent app-level event
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If Sh.ChartObjects.Count < gChtCnt Then
If gChtCnt = 0 Then
Set gCol = Nothing
Else
GetCharts Sh
End If
End If
End Sub

Typically after user creates a chart user then selects a cell (sooner or
later). In practice it's unlikely to be long before you've got all your
charts monitored.


..or deleted

Similarly no event is triggered when a chart is deleted. However if you are
already trapping the chart events, in the deactivate event you can attempt
to refer to some property of the Parent Chartobject. If that throws an
unexpected error it's reasonable to infer the selected chart has been
deleted.

Typically user selects a chart then hits the delete button. In that scenario
the error in the deactivate event almost confirms the chart has been
deleted. Although could "remove" the relevant chart (ie the withevents
class) from the collection might just as well recreate all entirely, a false
indication of the chart having been deleted would not be a problem.

Of course there are other ways to delete charts which would not trigger the
deactivate event, say deleting multiple charts in one go. However the cell
select event addition is a useful fallback.


MsgBox("hello")
in the cht_deactivate event, nothing happens either, which I don't
understand.

If the other events are working you must have the event wrong. Remove what
you have and reselect from the dropdown combo.
Put a break on the event, when it fires step through with F8. But before you
get to the OnTime line press F5. Also include a break in the GetCharts
routine to catch the OnTime call.


Realistically there will never be a bullet proof way to have all your charts
monitored exactly as & when created & deleted, but in practical terms you
can get pretty close.

Regards,
Peter T


"Matt McQueen" wrote in message
...
Peter,

Thanks for the continued input. I tried your original code in a new
spreadsheet. It works fine for sheetactivate and deactivate events and
when I
click on the chart it produces a message box with the sheet and chart
number.
However it does not produce an event when the chart is created or deleted.
I
had already accomplished this at the application level (to ensure I
capture
plots in other workbooks).

I tried your amended code... but it doesn't appear to do anything when the
chart is deactivated. I suspect user error on my part, since if I enter:

MsgBox("hello")

in the cht_deactivate event, nothing happens either, which I don't
understand.

Jon - typo, should of course be True for the Else case.

Cheers,

Matt