Allow user interaction with your spreadsheet mid macro – by tricking your VBA code into pausing

Background

Complexity: 35%
55%

Sometimes, you need your VBA code to pause mid process at a specified point – without losing everything it has already allotted to memory.

This could be after you generate a report, which requires end-user input before continuing. It could be to correct an error that has been detected on the sheet that can only be corrected by an end user (and not rectifiable through other means).

You can make VBA code “pause” while running without using the Stop command (which requires the end user to then press the Play button in the VBA window to continue).

The process involves invoking a userform, and then waiting for the userform to disappear – all while the VBA sits idly.

Solution

Step 1

Load the VBA window, press ALT+F11 in any Excel window, expand the file name on the left on the window that appears, and open the Userform folder. Then press Insert -> Userform.

Create a simple userform (or download the file from the bottom).

015_Img001

Step 2

Change the settings of the userform to allow user interaction.

Set ShowModal = False in the left hand properties window (if you can’t see it, press F4 or View -> Properties Window).

Set Name = “f_wait” or another name you want to use (you will need this for later).

015_Img002

ShowModal = False will now allow you to interact with the spreadsheet in the background when this userform has loaded – but it will not allow you to pause the VBA code.

Step 3

Invoke the VBA code as follows in a normal userform:


Sub YourCode()

'YOUR CODE HERE

Call AddPause

'REST OF YOUR CODE

End Sub

Sub AddPause()

' Written by: FormatCells.com
' For the best custom made spreadsheets, visit: www.formatcells.com

Application.ScreenUpdating = True

f_wait.Show

Do Until f_wait.Visible = False

DoEvents

Loop

Application.ScreenUpdating = False

End Sub

Application.ScreenUpdating allows the form to load and update its formatting etc without blocking out a part of the screen.

f_wait.show loads the userform.

Do Until f_wait.Visible = False
          DoEvents
Loop

This makes the VBA code pause. It tells the computer “keep checking until the form is no longer visible”. So, while the form is visible, the code will not continue.

However, this would normally lock out any user interaction, as is the normal VBA way i.e. while VBA is running (which is still is in this case) – you cannot use the spreadsheet for other processes.

By using DoEvents in the middle of the loop, you are preventing the code from locking out the spreadsheet. The official definition from MSDN is:

  • Processes all Windows messages currently in the message queue.

Processing messages normally stops when VBA code is running – but by allowing it to process more system messages from Excel, the spreadsheet becomes “unlocked” and lets the user make edits as much as they want.

Step 4

As soon as the user has finished with their updates, they can press the “Continue” button – which simply unloads the form.

The form has disappeared, so the rest of the code has the okay to continue!

Notes

You can’t run other macros while this process is running – even when paused. That is because the macro is technically still running – it’s just stuck in a never ending loop until the userform disappears.

The user should aim to continue as soon as possible – opening and closing other files may cause problems.

The Cancel button ends every macro being run. It completely ends every running macro, so you can start again from scratch.

The CPU is still being used, and will continue to be used – however on an Intel Core i3 machine with 8GB of RAM, CPU usage was hovering around 4%.