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
As the first step, I have created a Sub-procedure named MsgBox_disappeared_automatically. I have also set CreateObject(“WScript.shell”) as ObjectShell and Range(“E5:E14”) as Rng. Then, I used a For Loop to go through each cell within the defined range. If the cell value is above $70,000, I have considered it as an outstanding performance and highlighted it with a MsgBox.
Finally, run the code (press F5) and you will have your desired result like the following output shown in the video.
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
Here, I have defined the values of column 5 to loop through the cases mentioned in the Select Case until the cell value becomes empty. Based on the matched condition, it will pop up the result related to the condition in a MsgBox which will last for only 1 second and then get disappear.
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
In the above code under MsgBox_Timeout sub_procedure, I have set multiple conditions under Select Case where the values in column 4 will be evaluated. Based on the matched condition, all the values will pop up in the MsgBox one after another for 1 second period and will disappear automatically. It will last until all the values in column 4 go through the conditions.
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
First of all, I have declared a function named Sleep which will be available for Excel 64-bit. I have defined Windows Script Host Object and range. Then, I have gone through every value in column E via For Loop and applied a condition to have the matched result in MsgBox which will be disappeared after the timeout. I have set the timeout period for 1 second. After the disappearance of MsgBox, Excel will rest for 3 seconds which I restricted with the Sleep function.
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
Under the MsgBox_without_additional_buttons1 sub_procedure, I have set a range and highest value. Then, with the help of For Loop, I compared the values inside the range and find out the maximum value. In the MsgBox properties, I have defined the vbOKOnly constant which will add no other button rather than the OK button in the MsgBox.
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
I have set a range of values and tried to find out the lowest value in the MsgBox_without_additional_buttons2 sub_procedure. Here, I have compared the values inside the range and find out the minimum value with the help of For Loop. In the MsgBox properties, I have defined the vbSystemModal constant which will add no other button rather than the OK button in the MsgBox, and the vbInformation constant will add an information icon in front of the message.
Finally, we will have our desired output where no additional button will be added rather than the default OK button.
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
This VBA code includes a declaration for the MessageBox function using conditional compilation directives to account for the difference in pointer size between 32-bit and 64-bit versions of Microsoft Office. The If VBA7 Then directive checks whether the current version of VBA supports 64-bit pointers (which was introduced in VBA 7.0), and if so, the function declaration uses the LongPtr data type to represent pointer-sized integers. This part will help to have a modeless MsgBox. Then, I defined multiple columns with different range names and a variable named Response. Based on the click-on button, the value in the text boxes will be added or not.
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.
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.