Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 "Matt McQueen" wrote in message ... 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 "Peter T" <peter_t@discussions wrote in message ... 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 "Matt McQueen" wrote in message ... 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
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 "Matt McQueen" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
"Jon Peltier" wrote in message
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
"Jon Peltier" wrote in message
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ChartAdd event for embedded charts
"Matt McQueen" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2003 Embedded Charts | Excel Programming | |||
embedded charts | Charts and Charting in Excel | |||
setting an event in an embedded chart from a differnet workbook | Excel Programming | |||
Is there some way (event) to know when an embedded chart is deleted? | Charts and Charting in Excel | |||
Event class doesn't fire in embedded VBA | Excel Programming |