Check button

[Fred]

Hi,

I am running a long process and would the user to be able to stop/cancel the
process.
I can put a button on the sheet or one a userform but what code do I need to
insert in my process to detect if the button gets clicked?

Thanks
Fred

 

[Greg Glynn]

Hi,

I am running a long process and would the user to be able to stop/cancel the
process.
I can put a button on the sheet or one a userform but what code do I needto
insert in my process to detect if the button gets clicked?

Thanks
Fred

Hi Fred,

Try adding a DoEvents line into your loop code somewhere (assuming
your long process is looping). This enables EXCEL to detect user
activity.
Then you might want to force an EXIT SUB command.

 

[JLatham]

Start by declaring a public Booleann variable. We'll call it
stopTheProcessFlag.

At the beginning of your long process, set that flag = False.

Within your process, at appropriate locations, test to see if it has changed
to True and if so, gracefully abort the process (assuming that's what you
want to do when someone clicks the button).

For this one, I'd recommend a command button from the Controls Toolbox.
Once you put it on the sheet, double-click on it to open up the VB Editor to
its _Click event code.

Private Sub CommandButton1_Click()
stopTheProcessFlag = True
End Sub

Or if you want subsequent clicks of the button to toggle the flag:
Private Sub CommandButton1_Click()
stopTheProcessFlag = Not stopTheProcessFlag
End Sub

 

[Fred]

Thanks Greg, that is just what I was looking for.

 

[Copacetic]

This great little piece of code is a good tutorial for nearly any
called process where one has to make sure that no recursive looping
occurs, which could be a bad thing.

I can make a list of checkboxes that the button code can test the values
of and incorporate into its process pathways. "TheProcessFlag" can be
anything. The button click can call the checkbox tests and then decide at
which point in the code to continue or call new code. It is like an
if/then thing without so much if/then coding.

A cool little decision engine.

Thanks.