ChartAdd event for embedded charts

[Matt McQueen]

I have a command bar button whose enabled status I would like to depend on
the number of embedded charts within the activesheet in any workbook open in
Excel.

I have accomplished 2/3rds of this by trapping the Application.SheetActivate
and Application.WorkbookActivate events. However for a total solution I'd
like the button to be enabled/disabled when an embedded chart is
created/deleted on a sheet that had/has no embedded charts. There is no
ChartAdd event, and I tried messing around with Jon Peltier's handy advice
(http://www.computorcompanion.com/LPMArticle.asp?ID=221) but I don't think I
really understand what I'm doing since nothing happens when I try my code...

So far I had created a new class module called clmod_ChartDeactivate and
declared:

Public WithEvents ChartEvent as Chart

Then added the following to the ChartDeactivate event:
If activesheet.chartobjects.count = 0 then
application.commandbars("NRT").controls(4).enabled = False
Else
application.commandbars("NRT").controls(4).enabled = False
End if

where NRT is the name of my commandbar and the button I wish to
enable/disable is the fourth control.

Then I added the declaration:

Dim objChtDeactivate as New clmod_ChartDeactivate

to a module, along with the following code (stolen straight from Jon P.):

Dim clsEventCharts() As New clmod_ChartDeactivate

Sub Set_All_Charts()
' Enable events for all charts embedded on a sheet
' Works for embedded charts on a worksheet or chart sheet
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim clsEventCharts(1 To ActiveSheet.ChartObjects.Count)
Dim chtObj As ChartObject
Dim chtnum As Integer

chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
' Debug.Print chtObj.Name, chtObj.Parent.Name
Set clsEventCharts(chtnum).ChartEvent = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub

I then call Set_All_Charts in the workbook_open event of ThisWorkbook.
However when I deactivate a newly created chart nothing happens... any
pointers would be helpful.

Cheers

Matt

 

[Peter T]

As you say no events are triggered when creating or deleting a chartobject
(though an event is triggered when a chart-sheet is activated or added). So
it means using whatever other events are available, eg sheet activate or
workbook activate

Try the following in ThisWorkbook module, a normal module and a class module
as indicated

' thisworkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
GetCharts Sh
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set gCol = Nothing
End Sub

'' add similar event for workbook actrivate/deactivate

'''' in a normal module
Public gCol As Collection

Sub GetCharts(sht As Object)
Dim i As Long
Dim chtObj As ChartObject
Dim c As Class1

' code here to disable your button, or perhaps after
' If not gCol is nothing then.. etc

For i = 1 To sht.ChartObjects.Count
If i = 1 Then
Set gCol = New Collection
' code here to enable your button
End If
Set c = New Class1
Set c.cht = sht.ChartObjects(i).Chart
gCol.Add c, c.cht.Name
Next

End Sub

'' code in Class1
Public WithEvents cht As Chart

' select cht events from the top mid dropdawn then
' chart events from the top rt dropdown

Private Sub cht_MouseDown(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
MsgBox cht.Name
End Sub


Regards,
Peter T

 

[Matt McQueen]

Peter,

I catch your drift - however if the user doesn't click on the plot s/he just
created then I'd be left with the same problem.

I'll mess around a bit more and see if I can get the chart_deactivate to work.

Cheers,

Matt

 

[Peter T]

I don't follow. Apart from the obvious what use is it to you if user clicks
on the plot (I assume you mean to trigger the chart select event). If you
are already trapping the events so what, if you haven't yet trapped the
chart, user clicking or selecting it will not help to tell you if it's a new
chart just added.

In the chart_deactivate you could do say

on error resume next
s = cht.parent.name
on error goto 0
if len(s) = 0 then
the selected chart no longer exist & has just been deleted
Application.Ontime Now, GetCharts ' reset all charts events, if any exist

Regards,
Peter T

 

[Peter T]

In the chart_deactivate you could do say

on error resume next
s = cht.parent.name
on error goto 0
if len(s) = 0 then
the selected chart no longer exist & has just been deleted
Application.Ontime Now, GetCharts ' reset all charts events, if any exist

That should of course be
Application.Ontime Now, "GetCharts"

and change the following in GetCharts

Sub GetCharts(Optional sht As Object)
' code
If sht Is Nothing Then Set sht = ActiveSheet

Regards,
Peter T

 

[Jon Peltier]

I've spent some time trying to capture chart creation and deletion,
obviously not to the extent that Peter has. When I have a chance and the
need I'll revisit his approach.

Whether or not you've managed to capture the event, the code you've posted
will not make any changes, since in both the If and the Else, you are
setting the Enabled property of the control to false.

- Jon

 

[Matt McQueen]

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

 

[Peter T]

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

 

[Peter T]

I've spent some time trying to capture chart creation and deletion,
obviously not to the extent that Peter has.

Somehow I think that's unlikely <g>

Regards,
Peter T

 

[Peter T]

I've spent some time trying to capture chart creation and deletion,
obviously not to the extent that Peter has.

Somehow I think that's unlikely <g>

Regards,
Peter T

 

[Jon Peltier]

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

I knew it was a typo, because I'm an expert. I just was making sure the typo
occurred here and not in your code module.

- Jon