Creating A Message Box That Automatically Closes After A Specified Time Period

There are different ways to approach this, the best method I have found is to use using VBA code, this method utilises the windows script shell object pop up method. This is very similar to the normal VBA message box except that it allows you to add extra code to provide certain functionality such as a built in timer to close the box after a specified time duration.

    Download the already completed .XLSM file here

We can select the message box type from one of several types such as OK only, YES/NO, or OK & Cancel. There are others which you can choose from and then tailor your code to give a different response to each button the user may or may not press.

In the code below you will see the line:- "Keep Workbook Open", 4 + 32).

Here it is the value of 4 that selects the button type in the box, that is whether it only has an "OK" button or a "YES/NO" button or other arrangement. It is the value of 32 that selects whether there is a question mark, an exclamation mark or other icon in the box .

CLICK HERE  to see more details in microsoft's full documentation on the POP Up method.

In this code we will also tell the message box what to do when any of it's buttons are pressed or even if it is ignored. In this example we will use the equivelant of a "VB YES/NO" message box and program it as follows:-

In 3 above, the result would be -1 for no action (ignoring the message box). We could easily place an instruction to execute a command in the code to do whatever we like when this happens. We have chosen not to in this instance.

We will acomplish all this this using the following simple steps.

1. Insert a Standard Module    See how to do that here
2. Paste the following code into the module and hit save.

    
Option Explicit
Const PopupDurationSecs As Integer = 5

Sub StartTimer()
Application.OnTime Now + TimeValue("00:00:10"), "myShellMessageBox"
End Sub

Sub myShellMessageBox()

Dim Result As Integer

Result = CreateObject("WScript.Shell").PopUp( _
"Keep this workbook open?", PopupDurationSecs, _
"Keep Workbook Open", 4 + 32)
If Result = 6 Then
Exit Sub
ElseIf Result = 7 Then
Application.Quit
End If
End Sub
    
  

It is important that you realise at this point that you will need to edit the duration time for the box to stay open waiting for a response, and the time value for how long you want to elapse before the message box appears. You will need to tailor these to your own requirements.

We now need to tell the workbook to start the timer when it first opens.

Just go back again to the Project Explorer Pane in the VB Editor window, double click ThisWorkbook and just as you did with the Module1 paste the following code into the window that opens and again hit save.

    
Option Explicit

Private Sub Workbook_open()
StartTimer
End Sub
    
  

Now we will tell the workbook that it needs to start the timer again when something changes in a cell on the spreadsheet

Just go back again to the Project Explorer Pane in the VB Editor window, double click Sheet1 (Sheet 1) and just as you did above paste the following code into the window that opens and again hit save.

    
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
StartTimer
End Sub
    
  

The VB Editor window should now appear as below. Now hit save & close.

That's it all done! congratulations.