Search code examples
excelvbabuttonresumepause

Pausing/Resuming a Loop in Excel VBA (on Click)

So, I'm working with a rather large Excel sheet. I have created a lot of functions as it is fairly complex. When I tell it to Execute it takes quite a long time. I have created a timer that tells one how fare into the process it has come (illustrated on the picture):

Screenshot of Counter

Now. I would very much like to make a pause button. I have tried with:

Private Sub PauseButton_Click()
Application.Wait Now() + TimeValue("00:00:20")
End Sub

Which actually does pause the process. My initial thought was to enter a very large number and then resume on a button click. Unfortunately I can't resume (DoEvents) the process, as Excel seems compleatly frozen for the period entered.

If you have any ideas about how to fix this, they would be greatly appriciated.

Solution

  • Well, concerning that we do not have a view of the code of the form, you may try declaring a public boolean b_pause, which tells you whether you are paused or not. And write something like this:

    public b_pause as boolean

sub RunMe
    b_pause = True
    Do While b_pause
        DoEvents
    Loop
End sub

Sub StopMe()
    b_pause = False
End Sub