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

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

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

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

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

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


On Aug 28, 1:46*pm, Gary''s Student
wrote:
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