waiting (or pausing) for user selections - nested loops vs. serialexecution

[jpc_web]

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

 

[Gary''s Student]

Dim the variables that hold the properties in a public place. That is, in a
standard module above any other subs.

 

[jpc_web]

Thanks for your quick reply. I tried making the Boolean variable
public in the declarations section of the module holding the macro,
but I’m still having problems: selecting a chart or a cell doesn’t
seem to change the value of the (public) Boolean variables.

When my sub is not running, the following code, which is on the
worksheet’s code, will pop up a msgbox.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
b_cell_select = True
MsgBox "cell selected, b_cell_select = " & b_cell_select
End Sub

However, when the following macro is running, clicking on a cell in
the worksheet doesn’t seem to trigger the SelectionChange event, or
end the “Do…Loop”.

(This is copied from the module):
Option Explicit
Public b_chart_select As Boolean
Public b_cell_select As Boolean

Sub MatchChartSetup()
Dim height As Long
Dim width As Long
b_cell_select = False
b_chart_select = False

height = ActiveChart.Parent.height
width = ActiveChart.Parent.width

Do Until b_cell_select = True
While b_chart_select = True
ActiveChart.Parent.height = height
ActiveChart.Parent.width = width
b_chart_select = False
DoEvents
Wend
DoEvents
Loop

End Sub

I am very grateful for your help!
Thanks,
JP