Dim the variables that hold the properties in a public place. That is, in a
standard module above any other subs.
--
Gary''s Student - gsnu2007k


" wrote:

I've poked around the forum and not found a complete answer, so I
apologize if this is redundant or obvious.

What I'm trying to do is write a macro that will "copy" chart
properties (sizes, , fonts, axes scales, etc.) across charts. I was
planning to use a prototype chart as the source for these properties;
then as the user clicks on subsequent charts, each one gets the
properties of the prototype. When the user clicks on a cell, the
macro should end.

Thanks to Jon Peltier, I have created a chart events class that allows
trapping of events in embedded charts. I wrote code that sucessfully
changes global boolean variables if a chart is selected
(b_chart_select = true) or a cell is selected (b_cell_select = true).
What I'm stuck on, though, is how to "wait" for a user to click on
another chart.

It seems that user events (such as selecting a chart) are not detected
while code is running. Here's a snippet of code:

Do Until b_cell_select = True 'when this is true, user has clicked on
cell to end loop
While b_chart_select = True 'when this is true, the user has
clicked on a chart
'set chart properties
b_chart_select = False
DoEvents
Wend
DoEvents
Loop

However, while these loops are running, the chart_select and
selection_change events are not registered.

This makes me think that the correct approach would be to have some
stand-alone macros, such that when a chart is selected, a macro is
called. What confuses me here is how to pass chart properties from
the prototype to the newly selected chart. If the macros are stand-
alone, then any variables I have used to hold the prototype's
properties will not be available when the user clicks the next
(target) chart.

Any thoughts would be most appreciated!
Cheers,
JP