This post follows on from the Hello World Excel VBA post which is an introduction to the world of VBA. I urge you to quickly read that post before continuing here.

Here is the code created in that exercise:

Private Sub Workbook_Open()
    MsgBox "Hello World!"
End Sub

The above code exists in ThisWorkbook which is an object in the VBA Project. The simple MsgBox function employed in the previous example is now expanded upon:

Option Explicit
Private Sub Workbook_Open()
    Dim userResponse As String
    userResponse = MsgBox("Hello World!", vbOKCancel)
    If userResponse = vbOK Then
        ' all is good - open the Workbook as usual
       Exit Sub
    Else
        ' user cancelled - close this Workbook
        ActiveWorkbook.Close
    End If
End Sub
  1. The line Option Explicit ensures that all variables are declared — in the above example, this code is superfluous as no variables are declared. It is good coding practice to declare your variables.
  2. The Dim statement declares the variable userResponse as a string type.
  3. The value returned by the MsgBox() is then assigned to the variable userReponse using the = assignment operator.
  4. The value returned will be one of the constants; in this case if the OK button is clicked the constant vbOK is returned.
  5. The logic If ... Then ... Else ... End If is a very basic logic structure that should be manageable for any Excel user familiar with the =IF() function.

Have a look at the official documentation for more: https://docs.microsoft.com/en-gb/office/vba/Language/Reference/User-Interface-Help/msgbox-function

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.