How to get looping VBA code to respond to events on worksheet
 

Hello,

I have some code of the form

do while not g_Paused
bunch of calls
...
loop

where I'd like the variable g_Paused to be toggled by a button on the
worksheet. The only way I can seem to get the code to respond to the
button click is to use

do while not g_Paused
bunch of calls
...
ThisWorkbook.Worksheets("Name").Activate
DoEvents
loop

But this is pretty slow and causes the sheet to flicker...is there a
better method?


PS


I'm using buttons from the Control Toolbox


Thank you.

How to get looping VBA code to respond to events on worksheet
 

A quick way is to create a userform, with a button that sets the
g_paused = true

open the form show(vbmodeless)

then before your loop do
application.screenupdating = false

after your button is pressed

application.screenupdating = true.



"monkeyboy" wrote:

Hello,

I have some code of the form

do while not g_Paused
bunch of calls
...
loop

where I'd like the variable g_Paused to be toggled by a button on the
worksheet. The only way I can seem to get the code to respond to the
button click is to use

do while not g_Paused
bunch of calls
...
ThisWorkbook.Worksheets("Name").Activate
DoEvents
loop

But this is pretty slow and causes the sheet to flicker...is there a
better method?


PS


I'm using buttons from the Control Toolbox


Thank you.

How to get looping VBA code to respond to events on worksheet
 

Sorry, I should have been more explicit. I have a button on the
worksheet that sets g_Paused to TRUE, then calls the loop procedure. I
also have an "Pause" button on the worksheet that sets g_Paused to
FALSE. Once I enter the loop procedure however, the code no longer
responds to buttons on the worksheet, unless I say
"ThisWorkbook.Worksheets("Name").Activate ". When in the IDE, it
appears as if the event is firing as I can see the worksheet module
flicker when I push the "Pause" button, but the global g_Paused
doesn't seem to be set to FALSE? I have my code arranged in several
modules, with PUBLIC procedures and PUBLIC globals. I'll try using a
Form, but I don't understand what is stopping the worksheet event from
setting the variable...