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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"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