Excel In Your Office

Welcome to another post on VBA coding. In earlier post, we saw about variables. Now, we will see about message box which is another useful functionality to display messages to users. When I started digging in VBA, I used lot of message boxes to pause macro and see the status. Let’s see what a message box is and how to work with it.

A message box is a way that you can display some information to user or make the macro interactive. When you display a message box, a message is displayed to the user, macro will wait for user to click a button on the box and proceed. You will call the message box by following its syntax; just remember any of the Excel built-in functions we discussed already. Message box syntax is like below.

MsgBox (prompt[, buttons] [, title] [, helpfile, context])

Msgbox: This is the key word to get message box displayed.

Prompt: Mandatory. Whatever you enter in this argument will be displayed to the user. If you are entering a string in prompt, you will have to enclose that string with double quotes. You can also pass one of the variable value as an argument for this. Do you remember the line we used in variables post?

MsgBox ("You are signed in as " & UserName)

Buttons: Optional. If left omitted, your message box contains only one button which is OK (look at the above message). Options available for buttons are;

You can use more than one non-conflicting option in single message by adding them with a plus (+) sign. For example: if you want to have Yes, No & Cancel buttons (vbYesNoCancel) with Cancel as default button (vbDefaultButton3), you can write your code like below.

MyMsg = MsgBox("Do you want to proceed???", vbYesNoCancel + vbDefaultButton3)

You can use Value that is represented by the constant instead of constant itself. So for the above code, you can also write:

MyMsg = MsgBox("Do you want to proceed???", 3 + 512)

Title: Optional. This is the text shown on top of the message box. If omitted, “Microsoft Excel” will be shown.

Helpfile: Optional. If you notice, the alert produced by Excel will have either a help button or a question mark button on the alert. Those are to display a help file that is saved with the program. If you have a help file created for your message box, you can provide that here. But creating a help file deserves multiple separate posts but that’s not in scope for this post. You can conveniently ignore this for now.

Context: Optional. It is the number of the topic to be displayed from helpfile. If this is provided, helpfile is mandatory (quite obvious)

What It Returns

Once the message box is displayed to user, based on the user action, this syntax returns number from 1 to 7. You can proceed with any action using the user’s response. For example: in a box which displays two buttons viz. Yes and No, you can perform a set of actions if user clicks Yes and different actions if user clicks No. Possible return values are given below.

Examples

Let’s see 3 examples to see Msgbox in action. I will just give the code, a screenshot of the alert and simple explanation. Rest is on you to do research.

Example 1 : Show a simple OK button with a message.

Sub Test()
MsgBox ("Macro finished execution")
End Sub

Example 2 : Show two buttons with title (it is mandatory to assign this to a variable)

Sub Test()
MyMsg = MsgBox("Do you want to proceed???", vbYesNo, "Proceed Prompt")
End Sub

Example 3: Message box with a prompt; and 3 buttons viz. Yes, No & Cancel with Cancel highlighted by default and text aligned right side. Message box should be displayed as a warning (yellow triangle with a exclamation mark) Notice, I am using values rather than constants.