I am looking for a way to trap the case when an embedded chart is
deleted. I already have a chart Event class setup and am currently
handling Calculate events. I just need to know which event to trap on
a chart delete so that I can "disconnect" some things behind the scenes
that still think a chart is out there, when, in fact, it has been
deleted. Thanks in advance for any help.

Roy

AFAIK, there is no way to find out when an embedded chart is created or
deleted.

As far as deletions go, just don't worry about it. The chart events
associated with the chart will never happen.

As far as cleaning up goes, use the worksheet/workbook
activate/deactivate/open/beforeclose events.

If it is critical to find out about chart additions/deletions in
realtime, consider a 'OnTime' procedure (scanning the state of the
active window every second) -- or hook up with the OS's
SetTimer/KillTimer routines.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
I am looking for a way to trap the case when an embedded chart is
deleted. I already have a chart Event class setup and am currently
handling Calculate events. I just need to know which event to trap on
a chart delete so that I can "disconnect" some things behind the scenes
that still think a chart is out there, when, in fact, it has been
deleted. Thanks in advance for any help.

Roy

Well, I kind of have to worry about it. We have implemented dynamic
charts and have some things still laying around (data), including a
copy of a chart object (required to preserve some charting
information), that need to be cleaned up after the chart is deleted.
Since VBA doesn't provide some sort of event to trap deletes or a
destructor like the rest of the object oriented world, we have to
figure out something else.. A timer won't be sufficent or desirable
here. Thanks for the thoughts though.

roy

Hi ,

Add this to your chart event class.

'------------------------------------------------
Option Explicit

Public WithEvents MyCht As Chart

Private Sub MyCht_Deactivate()
'
' Checks the chartobjects name property
' If the chart has been deleted then this will cause an error
'
On Error GoTo ErrDeactivate
If Me.MyCht.Parent.Name < "" Then
MsgBox "Only deactivated"
End If
Exit Sub

ErrDeactivate:
MsgBox "Somebody deleted our chart", vbExclamation
Exit Sub
End Sub
'------------------------------------------------

Cheers
Andy

wrote:
Well, I kind of have to worry about it. We have implemented dynamic
charts and have some things still laying around (data), including a
copy of a chart object (required to preserve some charting
information), that need to be cleaned up after the chart is deleted.
Since VBA doesn't provide some sort of event to trap deletes or a
destructor like the rest of the object oriented world, we have to
figure out something else.. A timer won't be sufficent or desirable
here. Thanks for the thoughts though.

roy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks Andy. That should do it. I had read about the deactivate
event, but I just needed to catch the error when the name was either
null or inaccessable (chart object no longer valid since it was
delete).

Roy

Andy,
On other observation after testing your suggestion. It works fine if
you delete the chart from the UI. But if you have code that does a
programmatic delete (i.e. ChartObject.Delete) this event does not seem
to fire. I have an instance were I need to delete a chart
programmatically since the data that the chart represents is also being
deleted. When the data is deleted, as a good cleanup we also delete
the chart, but I would need this delete to also fire the Deactivate
event. Let me know if I am missing something here. Thanks again for
the help.

Roy

Hi,

Confused.
If the chart is being deleted by code then surely you know the chart is
being deleted, no?

If you actually just need to be able to execute the code thats in the
Deactivate event then move the code it into its own public routine
within the class. You can then use this new method.

Cheers
Andy

wrote:
Andy,
On other observation after testing your suggestion. It works fine if
you delete the chart from the UI. But if you have code that does a
programmatic delete (i.e. ChartObject.Delete) this event does not seem
to fire. I have an instance were I need to delete a chart
programmatically since the data that the chart represents is also being
deleted. When the data is deleted, as a good cleanup we also delete
the chart, but I would need this delete to also fire the Deactivate
event. Let me know if I am missing something here. Thanks again for
the help.

Roy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I've already done what you suggest -- and it works. We keep a copy of
the ChartObject (since we need to retain some information about the
chart that we can access in our Calculate event handler) around in a
collection and we have been deleting these when we delete the charts
programmatically -- no problem there. The problem was cleaning up
these orphaned ChartObjects for charts that were deleted from the UI.
So, I detect that these charts have been deleted during another update
operation, flag them as deleted, and clean them up at that point --
possibly some time after the chart delete from the UI has taken place.
I was just hoping for a clean solution to this in one place -- not two.
I like clean. I just get frustrated sometimes in VBA that you can't
get closer to the metal. Too many years vested in the WinAPI/MFC world
where you can control things a bit more! Thanks for the suggestions.
I may still use the Deactivate code and handle the other special case
by doing the cleanup manually.

Roy

Hi Andy,

That's a nice idea -- with limited applicability. If someone selects
the chartobject (or selects multiple charts which causes XL to
automatically select the associated chartobjects), the Deactivate event
is not triggered.

The only way that I have been able to think of to detect with certainty
the creation of new charts and the deletion of existing charts is to
have MS enhance the XL object model (or, of course, use a timer, which
can have its own set of issues).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi ,

Add this to your chart event class.

'------------------------------------------------
Option Explicit

Public WithEvents MyCht As Chart

Private Sub MyCht_Deactivate()
'
' Checks the chartobjects name property
' If the chart has been deleted then this will cause an error
'
On Error GoTo ErrDeactivate
If Me.MyCht.Parent.Name < "" Then
MsgBox "Only deactivated"
End If
Exit Sub

ErrDeactivate:
MsgBox "Somebody deleted our chart", vbExclamation
Exit Sub
End Sub
'------------------------------------------------

Cheers
Andy

wrote:
Well, I kind of have to worry about it. We have implemented dynamic
charts and have some things still laying around (data), including a
copy of a chart object (required to preserve some charting
information), that need to be cleaned up after the chart is deleted.
Since VBA doesn't provide some sort of event to trap deletes or a
destructor like the rest of the object oriented world, we have to
figure out something else.. A timer won't be sufficent or desirable
here. Thanks for the thoughts though.

roy

Hi Tushar,

Must admit I never tested for selected or multiple object deleting :)

Cheers
Andy


Tushar Mehta wrote:
Hi Andy,

That's a nice idea -- with limited applicability. If someone selects
the chartobject (or selects multiple charts which causes XL to
automatically select the associated chartobjects), the Deactivate event
is not triggered.

The only way that I have been able to think of to detect with certainty
the creation of new charts and the deletion of existing charts is to
have MS enhance the XL object model (or, of course, use a timer, which
can have its own set of issues).


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info