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

excel, userform, vba

Solution

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

Problem

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.

Original source