Timing problem with OnAction!

[Fred Russell]

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

 

[Jon Peltier]

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]

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

 

[Jon Peltier]

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/
_______