Faux Three-way Button: Start, Pause, Resume

The Challenge

Pausing and resuming macros describes how a userform and some VBA can Start, Pause, Resume, and Stop a process. This is important to users in two different situations.

• A process may run for a very long time and the user would like to be able to stop progress then restart where is was interrupted.
• A process may have an on screen indicator that the user can use to see if the process has reach a state where s/he knows it should be suspended or aborted, e.g. statistical analysis that reaches steady-state or production planning when the demand is greater than the ability to produce.

The Userform

The following userform has three controls

• cbtStart, A CommandButton that, when clicked, begins the process
• tbnPauseResume, A ToggleButton that suspends the process at a designed point, e.g.  at the end of the next cycle (loop) , and un-suspends the process, allowing it to continue.
• cbxStop, A Checkbox that signals the user wants to end the process.

Figure 1 Original Userform

Userform behavior

• Click the Start 
• Start is disabled.
• Pause and Stop are enabled
• With Pause and Stop enabled...
• Click Stop. 
• Macro reaches exit point and completes
• Resume and Stop are disabled
• Start is enabled.
• Click Pause. "Pause" becomes "Resume"
• With Resume and Stop enabled...
• Click Stop. Toggles between unchecked and checked
• Click Resume with Stop checked
• Macro execution stops at exit point, waiting for next input.
• Resume is changed to Pause
• Stop is set  to unchecked
• Pause and Stop are disabled
• Start is enabled
• Click Resume with Stop checked
• Execution continues
• Pause changed to 
• Resume becomes Pause

What was done

Changes to the userform

• Start and Pause/Resume buttons
• Made the same size
• Aligned on vertical and horizontal centers
• Grouped
• Stop command button added, same size as Start button
• Stop command button and Stop check box
• Aligned on vertical and horizontal centers
• Grouped
• Aligned on horizontal center with Start/Pause/Resume group
• Moved immediately below Start/Pause/Resume group

Userform behavior

At start-up, the only control shown is the Start command button.  All others are hidden.

Figure 1 Userform at start-up

When the Start button is clicked 

• Start command button is hidden.
• Pause/Resume toggle button is made visible.
• Stop command button is made visible.
• The ability to close the form is removed.

Figure 2 Process running

When the Pause/Resume button is clicked 

• Pause/Resume toggles to Resume.
• Stop command button is hidden.
• Stop check box button is made visible.

When the Stop button is clicked 

• Pause/Resume toggle button is hidden.
• Stop command button is hidden.
• Start command button is made visible.
• Macro exits at the designed exit point.
• The ability to close the form is re-instated.

Figure  3 Process Paused.

Figure 4 Process Paused.  Stop check box on.

When the Pause/Resume button is clicked again

• Pause/Resume button toggles to Pause.
• Stop command button is made visible.
• Stop check box is hidden.
• If the Stop check box is unchecked, processing continues.
• If the Stop check box is checked
• Macro exits at the designed exit point.
• Stop check box is set to unchecked.
• Pause/Resume toggle button is hidden.
• Start command button is made visible.
• The ability to close the form is re-instated.

The Code

ufmStartPauseResumeStop

Option Explicit

Private Sub cbtStart_Click()

    Me.cbtStart.Visible = False

    Me.tgbPauseResume.Visible = True

    Me.cbxStop.Visible = False

    Me.cbtStop.Visible = True

    Run "ControlWithFormEvents02"

End Sub

Private Sub cbtStop_Click()

    Me.cbxStop = True

End Sub

Private Sub tgbPauseResume_AfterUpdate()

    If tgbPauseResume Then

        Me.cbtStop.Visible = False

        Me.cbxStop.Visible = True

        Me.cbxStop = False

        tgbPauseResume.Caption = "Resume"

    Else

        Me.cbtStop.Visible = True

        Me.cbxStop.Visible = False

        tgbPauseResume.Caption = "Pause"

    End If

End Sub

Private Sub UserForm_Activate()

    Call SetFormToStartState(Me)

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

'   Prevents use of the Close button if the control macro called by the Start button is running

    If Me.tgbPauseResume.Visible And CloseMode = vbFormControlMenu Then

        MsgBox "Close button disabled during run."

        Cancel = True

    End If

End Sub

Code Module

Option Explicit

Sub StartPauseResumeStop_Visible()

    ufmStartPauseResumeStop.Show

End Sub

Private Sub ControlWithFormEvents()

    '  This is the basic control module

    '  Lines marked with "***" are stand-ins for the code that would do the real work

    Dim cel As Range  ' ***

    Set cel = ThisWorkbook.Sheets(1).Range("A1") ' ***

    cel.Parent.Activate ' ***

    cel.Select  ' ***

    Do ' Loop runs until the Check Box for stopping the run is clicked on _

         See comments in UserForm object module

        cel.Value = AddAndDivide(cel)  ' ***

        If ufmStartPauseResumeStop.tgbPauseResume Then

            Do

                DoEvents

            Loop Until Not ufmStartPauseResumeStop.tgbPauseResume

        End If

        DoEvents

    Loop Until ufmStartPauseResumeStop.cbxStop

    Run "SetFormToStartState(ufmStartPauseResumeStop)"

End Sub

Option Explicit

Function AddAndDivide(cel As Range)

    If Len(cel.Value) = 0 Then cel.Value = 0

    If IsNumeric(cel.Value) Then

        If CInt(Left(CStr(cel.Value), 1)) > 2 Then

            AddAndDivide = cel.Value / 2

        Else

            AddAndDivide = cel.Value + 1

        End If

    End If

End Function

Sub SetFormToStartState(frm As Object)

    frm.cbtStart.Visible = True

    frm.tgbPauseResume.Visible = False

    frm.tgbPauseResume = False

    frm.cbtStop.Visible = False

    frm.cbxStop = False

    frm.cbxStop.Visible = False

End Sub