MsgBox is one of the most widely used functions in Excel VBA Macro. It appears in a dialogue box that is an extra window on the screen. By default, every MsgBox has an OK button so that the user can press the OK button after viewing the desired output in the message box. In many situations, the user does not want to manually click the OK button. This is because clicking the OK button every time a MsgBox appears is time-consuming and sometimes stressful. Today, we will demonstrate how to use the Excel VBA Code to Click OK on Message Box Automatically. We will describe 2 easy methods to do the task.
Through the following short video, you can easily understand how we use VBA code to click ok on MsgBox.
How to Launch VBA Editor in Excel
VBA editor is the place where we write VBA Code. it is a separate window that stores the VBA code. There are multiple ways to launch VBA Editor in Excel.
i. Using Keyboard ShortCut to Launch VBA Editor
It is the easiest and quickest way to open VBA Editor. You can follow the below steps to do the task.
First of all, while staying in an Excel sheet press the ALT+F11 keys.
Therefore, you will see the VBA Editor window.
See the following video for a better understanding.
ii. Use of Developer Tab
You can use the Developer tab to bring out the VBA Editor window. To do so, follow the steps below:
First of all, go to the Developer tab.
Then from the Code group >> select Visual Basic.
Therefore, you will see the VBA editor window.
See the following video for a better understanding.
iii. Using View Code Option
You can also use the View Code option to launch the VBA editor.
First of all, we will right-click on the Worksheet tab>> select View Code from the Context Menu.
Therefore, you will see a VBA editor window.
See the following video to fully understand the procedure.
Overview of VBA MsgBox in Excel
Creating a VBA MsgBox is very easy. All you need to do is create a Sub procedure and insert a Msg Box in the Sub.
In the following code, you can see how simply we can create a MsgBox in VBA macro.
Sub Show_MessageBox()
MsgBox "Welcome"
End SubAfter running the code, it shows the output like the following:
i. VBA MsgBox Function Syntax
VBA MsgBox is a function, and therefore, it has syntax like other functions of Excel.
MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )
ii. MsgBox Arguments Explanation
The following picture shows the MsgBox argument explanations.
iii. MsgBox Icon Constants
The following picture shows the MsgBox icon constants.
Excel VBA Code to Click OK on Message Box Automatically: 2 Ways
In the following dataset, you can see that the Printer and Battery have no Price. Now, we want to include a MsgBox to show the product names that have no Price included. Also, we want that the code itself click the OK button until the last product is found.
To do so, we will describe 2 easy ways to click OK on a message box with Excel VBA. Here, we used Excel 365. You can use any available Excel version.
1. Create a Message Box as an Object and Set a Timer for It
In this method, we will create a msg box as an object and set a timer for it. We will create the Msgbox for 1 s, and after 1 second, it will click OK automatically.
The VBA code to click OK on the message box when the Msgbox is an object is given below.
Sub click_ok_on_message_box_automatically()
Dim time_set As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")
'Set the message box to close after 1 second
time_set = 1
Select Case MsgBox.PopUp("Hello There!", _
time_set, "Message Box will be gone after 1 Second", 0)
Case 1, -1
Exit Sub
End Select
End SubCode Breakdown
Sub click_ok_on_message_box_automatically()- We take click_ok_on_message_box_automatically as a Sub procedure.
Dim time_set As Integer, MsgBox As Object- We dec;lare time_set as Integer and MsgBox as Object.
Set MsgBox = CreateObject("WScript.Shell")- This line creates an object named “MsgBox” by calling the “CreateObject” method of the “WScript.Shell” class. This object will communicate with the message box.
time_set = 1- time_set variable is used to specify how long the message box will be displayed on the screen. Here, we set 1 second.
Select Case MsgBox.PopUp("Hello There!", time_set, "Message Box will be gone after 1 Second", 0)- The “Select Case” statement is used in this line to evaluate the result of the “PopUp” method of the “MsgBox” object. For the amount of time specified by the “time set” variable, the “PopUp” method displays a message box with the title “Message Box will be gone after 1 Second” and the message “Hello There!” (in this case, 1 second). The “0” parameter indicates that a “OK” button will be present in the message box.
Case 1, -1
Exit Sub- The “Select Case” statement evaluates the “PopUp” method’s return value. The return value will be 1 if the user clicks the “OK” button. The return value is -1 if the user closes the message box by clicking the “X” button or pressing the “Esc” key. In either case, the subroutine will exit without doing anything else.
After that, when we will click on the run button, a MsgBox will appear, and it will click Ok automatically.
Case 1: Clicking OK on Message Box to Find Out Product Name of Missing Price
In this example, we will describe how you can use the VBA code to click OK on the message box to find out product names that are missing prices.
First of all, we type the following code in the Module.
Sub Check_missing_Product()
Dim ws As Worksheet
Dim productRange As Range
Dim productCell As Range
Dim time_set As Integer
Dim MsgBox As Object
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set productRange = ws.Range("B5:B11")
For Each productCell In productRange
If productCell.Offset(0, 1).Value = "" Then
time_set = 1 'Set the message box to close after 1 second
Set MsgBox = CreateObject("WScript.Shell")
Select Case MsgBox.PopUp("Missing price value for " & productCell.Value, _
time_set, "Price Check", vbInformation + vbOKOnly)
Case 1, -1
End Select
End If
Next productCell
End SubCode Breakdown
Sub Check_missing_Product()- We take check_missing_product as the sub-procedure.
Dim ws As Worksheet
Dim productRange As Range
Dim productCell As Range
Dim time_set As Integer
Dim MsgBox As Object- We declare “ws” as a Worksheet object, “productRange” as a Range object, “productCell” as a Range object, “time_set” as an Integer, and “MsgBox” as an Object.
Set ws = ThisWorkbook.Worksheets("Sheet1")- This line instructs the “ws” variable to refer to the worksheet named “Sheet1” in the workbook containing the VBA code.
Set productRange = ws.Range("B5:B11")- This line configures the “productRange” variable to refer to the range of cells B5:B11 in the worksheet specified by the “ws” variable.
For Each productCell In productRange- This line begins a loop that will iterate through each cell in the “productRange” variable, assigning each cell to the “productCell” variable in turn.
If productCell.Offset(0, 1).Value = "" Then- This line determines whether the cell to the right of the current “productCell” cell is empty. If so, the code contained within the “If” statement will be executed.
time_set = 1- This line sets the “time_set” variable to 1, which will be used to determine how long the message box will be displayed.
Set MsgBox = CreateObject("WScript.Shell")- This line creates a new “WScript.Shell” object and assigns it to the “MsgBox” variable. The message box will be displayed using this object.
Select Case MsgBox.PopUp("Missing price value for " & productCell.Value, _
time_set, "Price Check", vbInformation + vbOKOnly)- This line displays a message box with the text “Missing price value for,” followed by the current “productCell” cell’s value. The message box will appear for the amount of time specified in the “time set” variable. The message box’s title will be “Price Check,” and it will include an information icon and an OK button. The “Select Case” statement captures the message box result (which button the user clicked).
Case 1, -1- This line defines the message box’s possible outcomes. The value 1 is returned if the user clicks the OK button. The value -1 is returned if the user closes the message box without clicking a button. Because the “Case” statement contains no code, the subroutine will simply proceed to the next iteration of the loop.
End Select- This line ends the “Select Case” statement.
End If- This line ends the “If” statement.
Next productCell- This line tells the loop to move on to the next cell in.
After that, when you run the code, it will display the message box with the product name, also it will click the OK button automatically.
Read More: Excel VBA: Develop and Use a Yes No Message Box
Case 2: Clicking OK on Message Box to Find Out Missing Price Address
In the following dataset, you can easily notice that Row 6 and Row 9 are missing Prices. Now, we will use the VBA code to click OK on the message box to find out the Row numbers that are missing prices.
In the first place, we type the following code in the Module.
Sub check_missing_prices()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lastRow As Long
lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
Dim i As Long
For i = 5 To lastRow
If IsEmpty(ws.Cells(i, 2)) Then
Dim time_set As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")
'Set the message box to close after 1 second
time_set = 1
Select Case MsgBox.PopUp("Price cell is blank in row " & i, time_set, "Warning", 0)
Case 1, -1
End Select
End If
Next i
End SubCode Breakdown
Sub check_missing_prices()- We declare check_missing_price as the Sub procedure.
Dim ws As Worksheet
Set ws = ActiveSheet- We declare “ws” as Worksheet, and we set Activesheet as ws.
Dim lastRow As Long
lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).RowAlso, we declare “lastRow” as Long, and set it to the last row of column B on the active sheet.
Dim i As Long
For i = 5 To lastRowDeclares i as a Long variable and starts a loop that will execute for each value of I from 5 to lastRow.
If IsEmpty(ws.Cells(i, 2)) Then- The IF statement determines whether the cell in the ith row and second column (i.e., column B) is empty.
Dim time_set As Integer, MsgBox As Object
Set MsgBox = CreateObject("WScript.Shell")- Declares the variables time set and MsgBox as integers and objects, respectively, and then sets MsgBox to a new instance of the WScript.Shell object.
time_set = 1
Sets the variable time_set to 1
Select Case MsgBox.PopUp("Price cell is blank in row " & i, time_set, "Warning", 0)
Case 1, -1
End Select- Displays a pop-up message box with a warning message and an OK button, and then waits for the user to click the button or time set seconds to pass. The Select Case statement examines the PopUp method’s return value and does nothing if the user clicks the OK button or if PopUp times out.
End If
Next i
End Sub- Ends the IF statement, loop, and subroutine.
After that, when you run the code, it will display the message box with Row number, also it will click the OK button automatically.
2. Create a Pop-Up MsgBox for Certain Time Limit
You can create a pop-up MsgBox for a certain time limit, and also you can set a time for the message box so that it clicks OK automatically.
First of all, we will type the following code in a Module.
Sub pop_up_message_box()
Set WshShell = CreateObject("Wscript.Shell")
WshShell.PopUp " Hello There! ", 1, _
"This Message Box will be gone in 1 second"
End SubCode Breakdown
Sub pop_up_message_box()- We take pop_up_message_box as the sub procedure.
Set WshShell = CreateObject("Wscript.Shell")- This line creates a new object called “WshShell” by calling the “CreateObject” method, which is used in Windows to create instances of objects. “Wscript.Shell,” is a built-in Windows object that gives access to a number of system features.
WshShell.PopUp " Hello There! ", 1, "This Message Box will be gone in 1 second"- This line calls the “WshShell” object’s “PopUp” method to display a message box with the text “Hello There!” and a timeout of one second.
Afterward, when we will run the code, a MsgBox will appear for 1 second, and it will click OK automatically.
Now, in Case 1, instead of Message Box, we can use the WshShell object to pop up a message, and therefore, we can click OK on the message box to find out product names that are missing prices.
To do so, we will type the following code in the module.
Sub POpupMessage_toCheck_missing_Product()
Dim ws As Worksheet
Dim productRange As Range
Dim productCell As Range
Dim time_set As Integer
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set productRange = ws.Range("B5:B11")
For Each productCell In productRange
If productCell.Offset(0, 1).Value = "" Then
time_set = 1 ' Set the pop-up message box to close after 1 second
Set WshShell = CreateObject("Wscript.Shell")
WshShell.PopUp "Missing price value for " & _
productCell.Value, time_set, "Price Check", vbInformation + vbOKOnly
End If
Next productCell
End SubAfter that, when you run the code, it will display the message box with the product name, also it will click the OK button automatically.
Code Breakdown
Sub PopupMessage_toCheck_missing_Product()We take PopupMessage_toCheck_missing_Product as the Sub procedure.
Dim ws As Worksheet
Dim productRange As Range
Dim productCell As Range
Dim time_set As Integer- We declare “ws” As Worksheet, “productRange” As Range, “productCell” As Range, and “time_set” As Integer.
- Set ws = ThisWorkbook.Worksheets(“Sheet1”)
The ws variable is set to the worksheet “Sheet1” in the workbook containing the VBA code.
Set productRange = ws.Range("B5:B11")- This line sets the productRange variable to the range of cells B5 to B11 in the worksheet ws.
For Each productCell In productRange- This line begins a loop that will iterate through each cell in the productRange range.
If productCell.Offset(0, 1).Value = "" Then- This line determines whether the cell adjacent to productCell (i.e., the cell in the next column) has an empty value.
time_set = 1 - This line sets the time set variable to 1, which will be used later to determine how long the pop-up message will be displayed.
Set WshShell = CreateObject("Wscript.Shell")
- This line creates a new object of type Wscript.Shell.
Set WshShell = CreateObject("Wscript.Shell")
WshShell.PopUp "Missing price value for " & _
productCell.Value, time_set, "Price Check", vbInformation + vbOKOnly- This line represents a pop-up message box with the text “Missing price value for [product name]” and the title “Price Check” (where [product name] is the value of productCell). The message box will appear for the amount of time specified by the time set (in this case, one second) and will include an “OK” button.
End If- This line ends the IF statement.
Next productCell- This line indicates moving on to the next cell in the productRange range.
Read More: Excel VBA to Display Message Box for 5 Seconds
How Do You Pop Up a Message Box Whenever You Click on a Certain Worksheet in Excel?
To pop up a message box whenever you click on a certain worksheet in Excel, you need to type the following code in ThisWorkbook.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
If Sh.Name = "Sheet2" Then
MsgBox "You are in Sheet2", vbInformation, "Welcome"
End If
End SubAfter that, whenever you click on Sheet2, a MsgBox will appear.
Read More: Pop Up Excel VBA MsgBox When Cell Meets Criteria
Frequently Asked Questions
1. What Clickable Buttons Are Possible on Excel Message Boxes?
There are several clickable buttons on message boxes. Based on the type of message boxes, these buttons can be used. The clickable buttons are
- vbOKOnly – by default, a message box always displays an OK By clicking on the OK button, the user will be able to dismiss the message.
- vbYesNo – This type of message box displays the Yes and No buttons. The user can click Yes to accept the message or No to reject it.
- vbOKCancel – This type of message box displays the OK and Cancel buttons. The user can accept or reject the message by clicking OK or Cancel.
- vbAbortRetryIgnore – This type of message box displays three buttons: Abort, Retry, and Ignore. The user can click Abort to cancel the process, Retry to restart it, or Ignore to skip it.
- vbYesNoCancel – This type of message box displays three buttons: Yes, No, and Cancel. The user can click Yes to accept the message, No to reject it or Cancel to cancel the process.
- vbRetryCancel – This type of message box displays Retry and Cancel buttons. The user can click Retry to restart the process or Cancel to cancel the process.
2. How to Add a New Line in Excel VBA MsgBox?
Use the VBA constant vbCrLf (Visual Basic Carriage Return Line Feed) between the text that you want to display on separate lines to add a new line in Excel VBA MsgBox.
See the following code:
Sub adding_Line_in_messagebox()
MsgBox "first line." & vbCrLf & "second line."
End SubWhen you will run the code, it will show the result as follows:
You can also use the vbNewLine to achieve the same result. See the following code:
Sub vbnewline_to_add_new_line()
MsgBox "first line." & vbNewLine & "second line."
End SubWhen you will run the code, it will show the result as follows:
Key Takeaways from This Article
This article is helpful for the readers to use the VBA code to click OK on the message box automatically. Let’s take a glance at what we learned from this article:
- You have learned how to create a VBA MsgBox.
- VBA MsgBox function syntax, argument explanation, contents, icons.
- You have found how to click OK automatically on MsgBox
- Got how to create a pop-up message box for a certain time limit
Download Practice Workbook
You can download the Excel file from the link below and practice the explained methods.
Conclusion
In this article, we extensively describe how to use Excel VBA Code to Click OK on Message Box Automatically. This article will help you to get rid of the situation when you need to click OK several times manually. We hope after reading this article, you find it useful for you. If you have any comments or suggestions, please let us know in the comment section.
Related Articles
- Excel VBA MsgBox Examples
- How to Show Range Address with MsgBox in Excel VBA
- Excel VBA: Create New Line in MsgBox
- VBA MsgBox That Will Automatically Disappear in Excel
