VBA MsgBox That Will Automatically Disappear in Excel

At the time of working with Microsoft Excel, we might face the necessity of disappearing MsgBox automatically. With the help of VBA, we can disappear MsgBox automatically in Excel. I have tried to summarize the whole process in the following video. For more explanation, you can go through the below section of the article.

How to Launch VBA Editor in Excel

We often create a module in order to work with VBA. First of all, we need to go to the Developer tab and select Visual Basic to create a module.

Once you have opened the editor, then click on the Insert tab and pick the Module option.

A module will be created and we can write the required code in that module to execute the program.

Create VBA MSgBox That Will Automatically Disappear in Excel: 2 Smart Ways

With the help of Windows Script host object along with For Loop, Do Until Loop, Select Case, and IF conditions, we can disappear MsgBox automatically with VBA. The methods are explained below with real-life examples.

1. Use Windows Script Host Object

A feature of the Microsoft Windows operating system called Windows Script Host (WSH) enables the execution of scripts on a computer. It provides a set of built-in objects that ease the access to modify the Windows registry. We can use the Windows Script Host Object to disappear MsgBox automatically. Just apply the code mentioned below.

Sub MsgBox_disappeared_automatically()
'Define Windows Script Host Object and Range
Set ObjectShell = CreateObject("WScript.shell")
Set Rng = Range("E5:E14")
'Use For Loop to go through each cell in the Range
For i = 1 To Rng.Cells.Count
    'Apply condition to show matched results in MsgBox
    If Rng.Cells(i).Value > 70000 Then
        ObjectShell.Popup "The Performance of " & Rng.Cells(i, -1).Value _
        & " is Outstanding.", 1, "Performance Evaluation"
    End If
Next i
End Sub

Finally, run the code (press F5)  and you will have your desired result like the following output shown in the video.

Read More: Excel VBA to Display Message Box for 5 Seconds

2. Showing Multiple MsgBox and Make Them Disappeared Automatically

Another simple way to automatically disappear MsgBox is to use Do Until Loop with Select Case and Windows Script Host Object. In this way, we can apply multiple conditions which will loop through all the values in the defined range and pop up results in the message box which will disappear automatically. In the above video, I have tried to show the overview of this whole concept. To do so, just apply the following code in the module and run the program.

Sub Disappear_MsgBox()
Dim i As Integer
i = 5
Do Until Cells(i, 5) = ""
    Select Case Cells(i, 5).Value
        Case Is > 80000
            CreateObject("WScript.Shell").Popup "The Performance of " & Cells(i, 3).Value & " is Outstanding.", 1, "Performance Evaluation"
        Case Is < 30000
           CreateObject("WScript.Shell").Popup "The Performance of " & Cells(i, 3).Value & " is not satisfactory.", 1, "Performance Evaluation"
        Case Else
           CreateObject("WScript.Shell").Popup "The Performance of " & Cells(i, 3).Value & " is up to the mark.", 1, "Performance Evaluation"
        End Select
        i = i + 1
   Loop        
End Sub

Click on the Run button to have all the results described in the video.

Read More: Excel VBA: Create New Line in MsgBox

How to Deal with VBA MsgBox Timeout in Excel

In order to work with MsgBox in VBA, we can set a timer after which the MsgBox will disappear automatically. To know more about this, go through the following section.

1. Disappear MsgBox After Specific Timeout

With the help of VBA, we can define the time period of a message box. After the defined time period, the MsgBox will be disappeared because of time out like in the above video.  To have that kind of result, just apply the following code with the defined dataset and run the code.

Sub MsgBox_Timeout()
Dim i As Integer
Set O_Shell = CreateObject("WScript.Shell")
i = 5
Do Until Cells(i, 4) = ""
    Select Case Cells(i, 4).Value
        Case 0 To 39:
            O_Shell.Popup Cells(i, 3).Value & " got F.", 1, "Final Result"
        Case 40 To 49:
           O_Shell.Popup Cells(i, 3).Value & " got D.", 1, "Final Result"
           Case 50 To 59:
           O_Shell.Popup Cells(i, 3).Value & " got C.", 1, "Final Result"
           Case 60 To 69:
           O_Shell.Popup Cells(i, 3).Value & " got B.", 1, "Final Result"
           Case 70 To 79:
           O_Shell.Popup Cells(i, 3).Value & " got A.", 1, "Final Result"
        Case Else
           O_Shell.Popup Cells(i, 3).Value & " got A+.", 1, "Final Result"
        End Select
        i = i + 1
   Loop        
End Sub

2. Timeout to Take Pause Between Continuous MsgBoxes

Sometimes we may need time to have a little break between continuous MsgBox. In the above video, I have tried to show a demo of it. My aim is to show a MsgBox which will disappear after the timeout and will pause for a little before the appearance of the next MsgBox. You can apply the following VBA code for this purpose.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Sub Timeout_MsgBox_with_Pause()
'Define Windows Script Host Object and Range
Set ObjectShell = CreateObject("WScript.shell")
Set Rng = Range("E5:E14")
'Use For Loop to go through each cell in the Range
For i = 1 To Rng.Cells.Count
    'Apply condition to show matched results in MsgBox
    If Rng.Cells(i).Value < 30000 Then
        ObjectShell.Popup "The Performance of " & Rng.Cells(i, -1).Value _
        & " is unacceptable.", 1, "Performance Evaluation"
        'wait for 3seconds until next output
        Sleep 3000
    End If
Next i
End Sub

How to Create Excel VBA MsgBox Without Additional Buttons

There is a built-in OK button in MsgBox. We can customize those buttons. We can even ignore the OK button and add some different buttons. There are two different ways to do so. We can use the vbOKOnly and vbSystemModal constants to do so.  They are described in the following sections.

1. Apply vbOKOnly Constant

In order to have no additional button in the MsgBox, we can apply the vbOKOnly constant as a MsgBox property. I have shown the actual output in the above video where only the OK button is shown with a defined output. No extra button is shown in the MsgBox. I have arranged a dataset with sales amounts. I have to show the maximum sales value in the MsgBox from those values. To do so, just apply the following VBA code.

Sub MsgBox_without_additional_buttons1()
Set Rng = Range("E5:E14")
Highest_sales = 0
For i = 1 To Rng.Rows.Count
    If Rng.Cells(i).Value > Highest_sales Then
        Highest_sales = Rng.Cells(i)
    End If
Next i
MsgBox "Highest sales amount is " & Highest_sales, vbOKOnly, "Sales Information"
End Sub

Pressing the Run button, we can have our desired output in the MsgBox.

2. Using vbSystemModal Constant

Another MsgBox property that we can use to have no additional button is the vbSystemModal constant. I have shown the overview of this button in the above video. Here, I have considered the sales amounts of every employee to find out the minimum amount and showed it in a MsgBox where no extra button will be shown. Just insert the following VBA code and press F5.

Sub MsgBox_without_additional_buttons2()
Set Rng = Range("E5:E14")
Lowest_sales = Rng.Cells(1).Value
For i = 2 To Rng.Rows.Count
    If Rng.Cells(i).Value < Lowest_sales Then
        Lowest_sales = Rng.Cells(i)
    Else
        Lowest_sales = Lowest_sales
    End If
Next i
MsgBox "Lowest sales amount is " & Lowest_sales, vbSystemModal + vbInformation, "Sales Information"
End Sub

Finally, we will have our desired output where no additional button will be added rather than the default OK button.

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

How to Create VBA Modeless MsgBox in Excel

In general, in cases when a MsgBox appears, there will be no way possible to perform other functions out of the MsgBox. If we can do other tasks keeping the MsgBox on, then the message box is called Modeless MsgBox. With the help of VBA, we can create a modeless MsgBox that is summarized in the above video. Here, I have created a UserForm with multiple text boxes which will take information from the users and add them to the respective columns. For this, I have created an empty UserForm first. To do so, click on the Insert tab first. Then, pick the UserForm option and a UserForm will be generated.

We can decorate the empty UserForm with Labels, TextBoxes, and Comand Button with the help of the Toolbox.

Then, double-click on the Command Button and write the following VBA code inside it to open up the Userform with the decorated properties.

#If VBA7 Then
    Private Declare PtrSafe Function MessageBox _
    Lib "User32" Alias "MessageBoxA" _
    (ByVal hWnd As LongPtr, _
    ByVal lpText As String, _
    ByVal lpCaption As String, _
    ByVal wType As Long) _
    As Long
#Else
    Private Declare PtrSafe Function MessageBox _
    Lib "User32" Alias "MessageBoxA" _
    (ByVal hWnd As Long, _
    ByVal lpText As String, _
    ByVal lpCaption As String, _
    ByVal wType As Long) _
As Long
#End If
Private Sub CommandButton2_Click()
Set Rng1 = Range("B" & Cells(Rows.Count, 2).End(xlUp).Row)
Set Rng2 = Range("C" & Cells(Rows.Count, 3).End(xlUp).Row)
Set Rng3 = Range("D" & Cells(Rows.Count, 4).End(xlUp).Row)
Dim Response As Long  
Response = MessageBox(0, "Are you sure about adding new informations?", "Confirmation Alert", vbYesNo + vbSystemModal) 
If Response = vbYes Then
    Rng1.Offset(1, 0) = TextBox1.Text
    Rng2.Offset(1, 0) = TextBox2.Text
    Rng3.Offset(1, 0) = TextBox3.Text
Else
        MsgBox "Informations not added"
End If
End Sub

Finally, we will have the newly added information in the table after the confirmation.

Frequently Asked Questions (FAQs)

1. How do I stop my message box from looping?

With the help of the VBA code, we can create a message box in the loop. If the message box needs to stop looping, we need to set the message box out of any kind of loop in the VBA code.

2. How can I lock my message box?

In order to lock the message box, we need to use the vbApplicationModal or vbSystemModal constant in the MsgBox function.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In the above article, I have tried to explain how to disappear MsgBox automatically along with MsgBox timeout, modeless MsgBox, and MsgBox with no additional buttons. To perform these tasks, I have used the Windows Script host object along with For Loop, Do Until Loop, Select Case, and IF conditions. I have tried my best to make it easy and understandable for the readers. It will be a matter of great pleasure for me if my article helps you even a little.

Related Articles

• Excel VBA MsgBox Examples
• Excel VBA: Develop and Use a Yes No Message Box
• How to Show Range Address with MsgBox in Excel VBA
• Excel VBA Code to Click OK on Message Box Automatically