Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there some way (event) to know when an embedded chart is deleted?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
Impedding/Overlaying Charts | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel |