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" wrote in message
...
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