Pausing a VBA loop to allow editing of worksheet, with or without userform

Question

I have a procedure that consists of several do and for loops and i would like to find an easy way to 'pause' the routine and allow the user to edit the sheet, with a msgbox or userform to resume execution where it left off.

I would like to do something like this

dim pause as boolean
pause=false
For i = 1 To 40
    Worksheets("sheet1").Range("A" & i) = i
    If i = 20 Then
        UserForm1.Show vbmodeless
          Pause = true
        Do until pause = false
        loop
    Else

    End If
    Next i


End Sub

Where the pause condition would be set by a sub on the userform. This do loop just crashes.

Ideally i would like the userform to have buttons that can run subs but also allow direct editing of cells while execution is paused.

Gary's Student Gary's Student · Accepted Answer · 2014-01-29T13:30:31

Here is a typical control structure that allows the user to perform some actions in the middle of a macro. When the user is done, they run OKToContinue to allow the macro to continue with the second part:

Dim AllowedToContinue As Boolean

Sub FirstPartSecondPart()
    AllowedToContinue = False
    MsgBox "allow user to perform actions"
    Do Until AllowedToContinue
        DoEvents
    Loop
    MsgBox "doing second part"
End Sub

Sub OKToContinuw()
    AllowedToContinue = True
End Sub

Pausing a VBA loop to allow editing of worksheet, with or without userform

1 Answers