How to Use a VBA Code in Excel to Click OK on MsgBox

In the following dataset, you can see that the Printer and Battery have no Price. We want to include a MsgBox to show the product names without a Price. We also want the code to click the OK button until the last product is found.

Method 1 – Create a MsgBox as an Object and Set a Timer for It

Steps:

Enter the following VBA code in the module:

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 Sub

Code 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 declare time_set as an Integer and MsgBox as an 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

The time_set variable specifies how long the message box will be displayed on the screen. Here, we set it to 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 an “OK” button 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.

Click on the run button.
A MsgBox will appear. It will click OK automatically.

Case 1: Clicking OK on the MsgBox to Find the Product Name of the Missing Price

Steps:

Enter 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 Sub

Code 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” “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.

 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 “Missing price value for,” followed by the current “productCell” cell’s value. The message box will appear for the time specified in the “time set” variable. The message box’s title will be “Price Check,” including 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. If the user clicks the OK button, the value 1 is returned, and if the user closes the message box without clicking a button, the value -1 is returned. 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.

When you run the code, it displays the message box with the product name and automatically clicks the OK button.

Case 2: Clicking OK on MsgBox to Find a 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 the Row numbers that are missing prices.

Steps:

Enter 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 Sub

Code 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).Row

Also, 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 lastRow

Declares 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

It 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 for the set number of 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.

When you run the code, it will display the message box with the Row number. It will click the OK button automatically.

Method 2 – Create a Pop-Up MsgBox for a Certain Time Limit

Steps:

Enter 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 Sub

Code 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 several 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 one-second timeout.

Run the code.
SMsgBox will appear for 1 second. It will click OK automatically.

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 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 Sub

When you run the code, it will display the message box with the product name and automatically click the OK button.

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 time specified by the time set (in this case, one second) and 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.

How to Pop Up a Message Box When You Click on a Certain Worksheet

Steps:

Enter 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 Sub