MsgBox in Excel VBA

In this post, you will be learning about MsgBox in Excel VBA, its types, uses, and the VBA line breaker and how to use MsgBox in Excel VBA.

The MsgBox function displays a pop-up style message box, where the users can give a command or perform an action with the help of the buttons present in it.

Uses of a MsgBox in VBA

• The MsgBox is a dialog box that is used to inform the users about the process happening on their workbooks.
• You can add buttons and redirect the buttons to a certain code, so when the user clicks it a certain action is performed.
• It provides interaction to the user with the workbook.

Variations of MsgBox in EXcel VBA

Basic MsgBox

Just by its name, the basic MsgBox normally displays a value or information with an ok button in it.

Let’s see it with an example.

The below code is a Hello World program, where the hello world is displayed in a MsgBox.

Hello World Application using VBA

• Under the Developer tab, click Visual basic.
• Select ‘Module’ from the Insert Menu
• Enter the following code in the tab

Sub Helloworld()

MsgBox "Hello World!"

End Sub

• Save it and press the run button.

MsgBox with YesNo

In this type, the MsgBox contains two buttons which will be Yes or No, where the user can select the option and it will perform the action as coded.

Let’s see it with an example.

Here, we are going to empty all the cells of the workbook using the MsgBox feature in Excel VBA.

Code

Sub Yes_or_No_MsgBox()

Dim answer As Integer

answer = MsgBox("We are going to delete the contents and show you the yes or no option?", vbYesNo + vbQuestion, "Empty Sheet")

If answer = vbYes Then

    Cells.ClearContents

Else

End If

End Sub

As we run the code, a MsgBox appears with the quoted text and requires an action, either Yes or No. On clicking yes, the contents in the workbook get deleted, if no then nothing happens.

Line Breaks in Excel VBA

In a lengthy line of VBA code, it may look like a long script with a collection of all the vowels which makes it hard to read it for other users.

To avoid this difficulty, we use the VBA Line Break Character, which is the underscore “_ “.

Let’s see it with an example.

For instance take the Hello world program code.

Code:

Sub Helloworld()

MsgBox "Hello World!"

End Sub

To break the line, follow these steps. ( It’s not  a big line of code but still.)

Line Break in a VBA Code

To enter a VBA line break you can use the following steps.

1. First, click on the character from where you want to break the line.
2. Next, type a space( ).
3. After that, type an underscore(_).
4. In the end, hit enter to break the line.

Code:

So the resulting code will be :

Sub Helloworld()

MsgBox _

"Hello World!"

End Sub

While running the code, it gets executed without any errors.

But there is one thing that you need to take care of; you can’t add a line break in the middle of the argument.