Excel VBA to Display Message Box for 5 Seconds

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for special tricks for applying Excel VBA to display a message box for 5 seconds, you’ve come to the right place. There are numerous ways to display messages for 5 seconds using VBA in Excel. Below we use VBA code to display MsgBox for five seconds before selecting all cells.


Idea Behind Displaying Message Box for a Specific Time in VBA

A message can be displayed for a set period of time using the Application.wait method in Excel VBA. This method pauses the VBA code execution for a defined amount of seconds, during which the message is shown. The VBA code continues to function after the set time has passed, and the message vanishes. We can also declare a variable called Duration in Excel VBA to hold the message box’s duration in seconds and set its value to a predetermined number of seconds. The message box is made using the PopUp method of the WScript.Shell object.


How to Launch VBA Editor in Excel

We can easily launch the VBA editor in Excel. First, We have to open the Customize Ribbon option from the Excel Options directly. We can open the Customize Ribbon option by following the process.

Firstly, right-click on any option of the ribbon. Thus, a wizard will appear.

Next, click on Customize the Ribbon.

Select Customize the Ribbon option

Consequently, you’ll see the Customize Ribbon option in the Excel Options. Have a look at the following picture.

click on Customize ribbon

We can import or export any tabs in the ribbon by customizing them. Personalizing the ribbon helps you to maintain the correct order you want to organize them. We can also hide some tabs if we don’t want them, or we can unhide them from the Customize Ribbon also. Utilizing the Customize Ribbon option, we can also display the Developer tab, where we can do our VBA and macros.

Now, go to the Customize Ribbon >> check the Developer box >> select OK.

click on Developer

Finally, the Developer tab will appear in the ribbon bar.

get the Developer tab

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

select Visual Basic tab

  • VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then click on Module from the drop-down.
  • This is how you will be able to launch the VBA editor.

click on Insert Module


Excel VBA to Display Message Box for 5 Seconds: 3 Suitable Cases

In the following section, we will use three effective and tricky examples to display a message box for 5 seconds using Excel VBA. This is Microsoft’s event-driven programming language called Visual Basic for Applications (VBA). To use this feature, you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Display Msgbox for 5 Seconds Before Selecting All Cells

Here, we’ll use an Excel VBA code to select only the visible cells from a dataset that includes the mouse production cost. In this example, we will use VBA code to display MsgBox for five seconds before selecting all cells. The outcome will resemble the picture shown above. The code below has been used to accomplish that.

Sub show_message_5_seconds()
Dim r1ng As Range
Dim Duration As Integer
Dim Message As Variant
Set r1ng = Sheets("Sheet1").UsedRange.Find("Body")
r1ng.CurrentRegion.Select
Duration = 5
Message = CreateObject("WScript.Shell").PopUp("Now, Visible Cells will be selected", Duration, "")
End Sub

inserting the VBA code in module section

By pressing F5 at this point or by selecting the Macro window from the Developer tab, you can run the code. The results will therefore be as follows, with the MsgBox displaying for 5 seconds before selecting the visible cells.

message box shows for 5 seconds

🔎 VBA Code Explanation:

Sub show_message_5_seconds_before_selecting_cells()

First of all, provide a name for the sub-procedure of the macro.

 Dim r1ng As Range
Dim Duration As Integer
Dim Message As Variant

Next, declare the necessary variable for the macro.

Set r1ng = Sheets("Sheet1").UsedRange.Find("Body")
r1ng.CurrentRegion.Select

It returns the range used on the specified worksheet as a range object, and finally, by using the find method, we will get the specified range.

Duration = 5
Message = CreateObject("WScript.Shell").PopUp("Now, Visible Cells will be selected", Duration, "")

The “PopUp” method of the “WScript.Shell” object is used to display a pop-up message in the following line. “Now, Visible Cells will be Selected” is the message that appears in the pop-up, and it will remain visible for 5 seconds. The “Message” variable holds the outcome of this method call.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Excel VBA: Create New Line in MsgBox


2. Insert Multiple Rows and Display Confirmation Message Box for 5 Seconds

Here, we’ll use an Excel VBA code to add multiple rows from the active cell in our Excel dataset. Also, we will use VBA code to display inserting multiple rows MsgBox for five seconds. The result will look similar to the image above. To do that, the following code has been employed.

Sub InsertRowsfromUser()
iMsg = InputBox("How Many Rows to Insert? (100 Rows maximum)")
numRows = Int(Val(iMsg))
If numRows > 100 Then
numRows = 100
End If
If numRows = 0 Then
GoTo EndInsertRows
End IfDo
Selection.EntireRow.Insert
iCount = iCount + 1
Loop While iCount < numRows
EndInsertRows:
Dim AskTime As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")
AskTime = 5
Select Case MsgBox.PopUp("A certain number new rows are inserted", AskTime, "Message Box", 0)
Case 1, -1
Exit Sub
End Select
End Sub

inserting VBA code

You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab.

Write the row number (we wanted to insert 3 rows, so we wrote 3) and click OK.

inerting 3 rows in the input box

You will get 3 newly inserted rows from the active cell in your dataset.

The results are as follows, with the MsgBox stating that a certain number of new rows have been added and displayed for five seconds.

display message box after inserting rows

🔎 VBA Code Explanation:

Sub InsertRows_message_for_5_Seconds()

First of all, provide a name for the sub-procedure of the macro.

iMsg = InputBox("How Many Rows to Insert? (100 Rows maximum)")

This code shows an input box asking the user to enter how many rows they want to insert.

  numRows = Int(Val(iMsg))
Next, it will change the string input to an integer.
If numRows > 100 Then
numRows = 100
End If
If numRows = 0 Then
GoTo EndInsertRows
End If

If the user inputs a value larger than 100, this If statement code restricts the number of rows to 100. If the user enters 0 rows, then the IF statement will go to the EndInsertRows label.

Do
Selection.EntireRow.Insert
iCount = iCount + 1
Loop While iCount < numRows
EndInsertRows:

For each iteration, this loop adds a new row to the worksheet.

Dim AskTime As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")
Next, the above code uses the Windows Scripting Host to construct a message box object.
'Set the message box to close after 5 seconds
AskTime = 5
Select Case MsgBox.PopUp("A certain number new rows are inserted", AskTime, "Message Box", 0)
Case 1, -1
Exit Sub
End Select
End Sub

This code shows the message box with the specified text for 5 seconds.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Pop Up Excel VBA MsgBox When Cell Meets Criteria


3. Display Data Type of Selected Cell for 5 Seconds in a Message Box

Here, we’ll use an Excel VBA code to display selected cell data types in our Excel dataset. Also, this code will display MsgBox for five seconds. Similar to the image above, the outcome will appear. This has been accomplished using the following code.

Sub CheckSelectedCellType()
Dim myCell As Range
Set myCell = Selection.Cells(1) ' Get the first selected cell
Duration = 5
' Check the data type of the cell's value
Select Case VarType(myCell.Value)
Case vbInteger
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains an integer.", Duration, "")
Case vbLong
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a long integer.", Duration, "")
Case vbSingle
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a single-precision floating-point number.", Duration, "")
Case vbDouble
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a double-precision floating-point number.", Duration, "")
Case vbDate
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a date or time value.", Duration, "")
Case vbString
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a text string.", Duration, "")
Case Else
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a value of an unknown type.", Duration, "")
End Select
End Sub

inserting VBA code in VBA window

You can run the code at this point by pressing F5 or by choosing the Macro window from the Developer tab. Following are the results, with the MsgBox indicating the data type of the chosen cells and displaying for five seconds.

display selected cell’s data type for 5 seconds

🔎 VBA Code Explanation:

Sub CheckSelectedCellType()

First of all, provide a name for the sub-procedure of the macro.

Dim myCell As Range

Next, declare the necessary variable for the macro.

  Set myCell = Selection.Cells(1) ' Get the first selected cell
Duration = 5
Select Case VarType(myCell.Value)
Case vbInteger
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains an integer.", Duration, "")
Case vbLong
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a long integer.", Duration, "")
Case vbSingle
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a single-precision floating-point number.", Duration, "")
Case vbDouble
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a double-precision floating-point number.", Duration, "")
Case vbDate
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a date or time value.", Duration, "")
Case vbString
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a text string.", Duration, "")
Case Else
Message = CreateObject("WScript.Shell").PopUp("The selected cell contains a value of an unknown type.", Duration, "")
End Select

In order to determine the value’s data type, these lines employ a Select Case statement. Based on the data type, a message box is shown using the WScript.Shell object’s PopUp method.

End Sub

Finally, end the sub-procedure of the macro.

Read More: Excel VBA Code to Click OK on Message Box Automatically


How to Create a VBA Message Box That Will Automatically Disappear in Excel

This example demonstrates how to instantly close a message box after a predetermined amount of time. This example shows a message box for specified seconds before automatically closing it.

Sum Messagebox_disappear
Dim Duration As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")
Duration = 2
Select Case MsgBox.PopUp("Message box will disappear automatically", Duration, "Message Box", 0)
Case 1, -1
Exit Sub
End Select
End Sub

Using the Duration variable, this code causes the message box to vanish. If we set the Duration variable to 2, the message box will automatically close after 2 seconds of the display. The result will look similar to the image below.

Read More: VBA MsgBox That Will Automatically Disappear in Excel


How to Disable Message Box in Excel VBA

The following code should be put at the start of your subroutine to disable message boxes in Excel VBA:

Application.DisplayAlerts = False

With the help of this code, Excel’s default behavior of showing alert messages for specific actions, like deleting a worksheet or overwriting data, will be disabled.

Sometimes you need to turn on the message boxes again. In that case, you can use the following code to enable message boxes once more:

Application.DisplayAlerts = True

Things to Remember

If the Duration variable is removed, the user has to click on an Option, such as Yes MsgBox, to remove the MesgBox Control.


Frequently Asked Questions (FAQ)

  • How do I display a message for a specific time in Excel VBA?

The PopUp method of the WScript.Shell object allows you to display a message in Excel VBA for a predetermined period of time. The PopUp method automatically closes a message box after it has been displayed for a predetermined amount of time. Here’s an illustration of how to apply it:

Duration = 10
Message = CreateObject("WScript.Shell").PopUp("Give your message here", Duration, "Title")
  • Can I customize the message box style and icon?

Yes, you can customize the message box style and icon in Excel VBA. Here, we have used a combination of constants (vbYesNo, vbQuestion, and vbDefaultButton2) to show a dialog box with a Yes button, a No button, and a Question mark icon. The default button (vbDefaultButton2) will be the second button (No button).

Alike, bring the code into the module.

Sub GetResult()
Main_Config = vbYesNo + vbQuestion + vbDefaultButton2
Result = MsgBox("Do you Want to see the monthly report?", Main_Config)
If Result = vbYes Then MsgBox "You are currently viewing the Report", vbInformation
If Result = vbNo Then Exit Sub
End Sub

After executing the code, we can see the MsgBox with a question mark. Then, click Yes to proceed. In the next step, we can see another Information icon on the prompt. Then, click OK to close the MsgBox.

  • Can I run the macro automatically when the workbook opens?

Yes, you can run a macro automatically when the workbook opens in Excel VBA. To do that, you have to double-click on the “ThisWorkbook” object to open the code module for the workbook and save the following code. To automatically run a macro when the workbook opens, use the code shown in the following example:

Private Sub Workbook_Open()
MsgBox "Welcome to New Workbook!", vbInformation, "Workbook is Opened"
End Sub

In this case, when the workbook is opened, the macro shows a welcome message.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the whole process.


Conclusion

That’s the end of today’s session. Now, I strongly believe that from now on, you may be able to apply Excel VBA to display a message box for 5 seconds. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo