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

On Apr 15, 9:45*am, "Fred" <repl...@newsgroup.please> wrote:
> 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.

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" wrote:

> 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
>
>
> .
>

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


"Greg Glynn" <(E-Mail Removed)> wrote in message
news:e22f245d-1fb0-4457-92e4-(E-Mail Removed)...
> On Apr 15, 9:45 am, "Fred" <repl...@newsgroup.please> wrote:
>> 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
>
> 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.

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.



On Wed, 14 Apr 2010 19:44:01 -0700, JLatham
<(E-Mail Removed)> wrote:

>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" wrote:
>
>> 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
>>
>>
>> .
>>