Monday, October 13, 2014

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


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"
        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()
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
            Loop Until Not ufmStartPauseResumeStop.tgbPauseResume
        End If
    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
            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