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

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.

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.

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.

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.

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.