Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Timing problem with OnAction!
I'm using VBA to draw 4 charts to a worksheet that I add to the project
using: ActiveSheet.ChartObjects.Add After this happens there is a SUB on the ActiveSheet to change the size of each of the 4 charts by clicking on a chart - expands/retracts the chart size to show all 4 charts or just 1 chart enlarged. The problem lies in setting the Chart1_Click SUB to the chart with: ActiveSheet.Shapes(1).OnAction = "Sheet1.Chart1_Click" If I add this code to the end of the chart draw routine, the routine goes into an endless loop. I believe this is because the ActiveSheet and charts are not drawn at that point in time - it doesn't exist yet. If I add the OnAction assignment to the ActiveSheet SUB WorkSheet_Activate it works fine but not before selecting another worksheet, then returning to activate the chart worksheet. This latter solution sucks! I've tried adding a 10 second delay but it still won't work properly. Is this a case for writing a class macro to record the event of the chart sheet reaching completion? I've never been there before. Help! What's the best way to solve this problem? Best regards, Fred |
#2
|
|||
|
|||
Timing problem with OnAction!
Maybe you could use a global boolean that your code checks before
running the resizing code. The chart drawing and resizing procedures should each be able to make it true to allow resizing or false to deny it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Fred Russell wrote: I'm using VBA to draw 4 charts to a worksheet that I add to the project using: ActiveSheet.ChartObjects.Add After this happens there is a SUB on the ActiveSheet to change the size of each of the 4 charts by clicking on a chart - expands/retracts the chart size to show all 4 charts or just 1 chart enlarged. The problem lies in setting the Chart1_Click SUB to the chart with: ActiveSheet.Shapes(1).OnAction = "Sheet1.Chart1_Click" If I add this code to the end of the chart draw routine, the routine goes into an endless loop. I believe this is because the ActiveSheet and charts are not drawn at that point in time - it doesn't exist yet. If I add the OnAction assignment to the ActiveSheet SUB WorkSheet_Activate it works fine but not before selecting another worksheet, then returning to activate the chart worksheet. This latter solution sucks! I've tried adding a 10 second delay but it still won't work properly. Is this a case for writing a class macro to record the event of the chart sheet reaching completion? I've never been there before. Help! What's the best way to solve this problem? Best regards, Fred |
#3
|
|||
|
|||
Timing problem with OnAction!
Jon,
Thanks for your help. I've browsed your web site numerous times and learned a lot about charting. Your site is my No. 1 resource. Through trial and error I finally figured out the problem as follows: The assignment of the Chart_Click() macros must occur before the actual macros are written to the active sheet as in the example below. Intuition tells me that I should write the macros to the chart sheet first and then assign the OnAction statements. Wrong! Private Sub AddEventMacros() Application.ScreenUpdating = False Dim StartLine As Long ActiveSheet.ChartObjects(1).OnAction = ActiveSheet.CodeName & ".Chart1_Click" ActiveSheet.ChartObjects(2).OnAction = ActiveSheet.CodeName & ".Chart2_Click" ActiveSheet.ChartObjects(3).OnAction = ActiveSheet.CodeName & ".Chart3_Click" ActiveSheet.ChartObjects(4).OnAction = ActiveSheet.CodeName & ".Chart4_Click" 'Add macros to new graph sheet On Error GoTo ErrorHandler Set vbP = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule With vbP StartLine = .CountOfLines + 1 .InsertLines StartLine, myMacro1 & myMacroCht1 & myMacroCht2 & myMacroCht3 & myMacroCht4 End With Exit Sub ErrorHandler: 'Resume begins with the statement that caused the error, don't change me Resume End Sub Thanks again, Fred |
#4
|
|||
|
|||
Timing problem with OnAction!
Hi Fred -
I've always had mixed results trying to write code on the fly. You can put your chart event code into a separate class module, then hook it to your chart sheet when you create (or activate) the chart. I always use a class module for the chart events, even for a chart sheet (unless I'm just doing a quick demo). It's just too easy to change the location of the chart to an embedded chart, then remembering the lost code. In my article about chart events, I describe this class module approach. You can assign any chart to the macro, not just an embedded chart as in the examples. http://www.computorcompanion.com/LPMArticle.asp?ID=221 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Fred Russell wrote: Jon, Thanks for your help. I've browsed your web site numerous times and learned a lot about charting. Your site is my No. 1 resource. Through trial and error I finally figured out the problem as follows: The assignment of the Chart_Click() macros must occur before the actual macros are written to the active sheet as in the example below. Intuition tells me that I should write the macros to the chart sheet first and then assign the OnAction statements. Wrong! Private Sub AddEventMacros() Application.ScreenUpdating = False Dim StartLine As Long ActiveSheet.ChartObjects(1).OnAction = ActiveSheet.CodeName & ".Chart1_Click" ActiveSheet.ChartObjects(2).OnAction = ActiveSheet.CodeName & ".Chart2_Click" ActiveSheet.ChartObjects(3).OnAction = ActiveSheet.CodeName & ".Chart3_Click" ActiveSheet.ChartObjects(4).OnAction = ActiveSheet.CodeName & ".Chart4_Click" 'Add macros to new graph sheet On Error GoTo ErrorHandler Set vbP = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule With vbP StartLine = .CountOfLines + 1 .InsertLines StartLine, myMacro1 & myMacroCht1 & myMacroCht2 & myMacroCht3 & myMacroCht4 End With Exit Sub ErrorHandler: 'Resume begins with the statement that caused the error, don't change me Resume End Sub Thanks again, Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) |