The VBA message box (msgbox) helps convey a pre-defined messages to a user. Developers often use the message box for stuff ranging from debugging vba (msgbox “I am here” types) to prompting users for action (msgbox (“Sorry, what you asked for could not be done”, vbAbortRetryIgnore)). While it remains one of the most useful and frequently used commands in vba, it is also common to find developers being stuck with the most primitive form of the message box and losing out on the opportunity to customize it. In this article we look at how you can enhance the VBA message box and make it more effective.
The basic form of the Message Box (msgbox) in VBA
1 2 3 | Sub basic_messagebox() MsgBox "Hi there" End Sub |
Time honored and tested, this is the basic form of the message box. This form is something that anyone who has written more than 5 lines of VBA code is familiar with. This message box simply shows a simple message to the user. All you have to do make this work is to specify the message at the end of the msgbox command in your vba code. However, there are many ways in which this simple message box can be customized.
Customizing the VBA Message Box
Let’s explore a few ways in which we can customize the message box. Before we go further, let’s examine the complete syntax of the message box:
Return value = MsgBox(Prompt, Button and Icon types, Title, Help File, Help File Context)
Where:
Return Value – Indicates the action the user took when the message box was shown to him/her.
Prompt – It is the message contained in the main body of the message box.
Button and Icon Types – This specifies the set of buttons & Icons and their placement as they would appear to the user.
Help File – This is the path to a help file that the user can refer to on this topic.
Help File Context – This is the pointer to that part of the help file that specificially deals with this message.
Let’s take an example that will provide greater clarity.
1 2 3 | Sub basic_messagebox() i = MsgBox("Do you wish to be terminated ?", vbAbortRetryIgnore, "Greetings Earthlings", "test.hlp", 100) End Sub |
The output of the above piece of code will be something like this:
Let’s take each of part of the message box and see how we can customize it.
How to customize the buttons in a VBA message box
Rather than showing the user the plain vanila “Ok” button, you can equip your message box with buttons that make the user feel that he/she is making an intelligent choice.
The example above exaggerates but only slightly so. It is all common to find developers leaving OK only options for unsuspecting users to click. Since vba provide the ability to catch the return value (the choice made by the user), there’s no reason why any reasonable developer would not want to catch that input and provide the user with greater control over how the program responds.
Here is a list of various buttons and icons you can use in the vba message box.
You can even combine two or more buttons (and icons) by simply using the + symbol. In the example given below, we combine the ok button with the critical icon and show the combination to the user. (In addition, You can also specify which button has to have the default focus by using the vbDefaultButton options from the above chart)
1 2 3 | Sub basic_messagebox() i = MsgBox("Hi there", vbOKOnly + vbCritical) End Sub |
Capturing the user action from a vba message box
As highlighted in the sections above, the message box provides the ability to capture the user action and take further action based on that feedback. You can store the output of a vba message box in a variable and then take appropriate action based on the user’s choice. Here’s a list of return values associated with the user clicking various buttons in a message box.
Inserting multiple lines in a VBA message box
Suppose you had a long message that you wanted to show in multiple lines. Since the message enclosed in quotes is typically a string, you can use the vba new line character (vbCrLf) to seperate out various parts of a long message into multiple lines. Here is the code:
1 2 3 | Sub basic_messagebox() MsgBox "This is the first line" & vbCrLf& "This is the second line" End Sub |
And this is how it would appear to the user:
Inserting special characters in a VBA message box
Often times, while working with other languages, you may want to insert characters outside of the 26 English characters. In such cases, you can simply use the VBA char function (chr) to insert the special characters.
1 2 3 | Sub basic_messagebox() MsgBox Chr(200) & "spani" & Chr(244) & "l" End Sub |
In the example above, we have used two special characters to create a string which has non-english alphabets.
Prevent Users Ctrl Break (ing) Your VBA Code During Execution 
Multithreaded VBA – An Approach To Processing Using VBScript 
VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code 
Treemap in Excel – Coming Soon 
VBA Select Case – Using VBA Select Case Statement in Excel 
Control Chart in Excel Using VBA – Six Sigma Control Chart Code & Software 
I encapsulated the MsgBox function, I like it better than having MsgBox calls littering my code. I posted an example on my blog recently.
August 12th, 2009 at 7:40 amYou should show how to get the user’s response:
2
3
4
5
6
7
8
9
Response = MsgBox("Do you wish to be terminated?", vbYesNo + vbQuestion)
Select Case Response
Case vbYes
' terminate
Case vbNo
' do not terminate
End Select
Jon,
Thanks for providing the implementation.
JP,
Taking a cue from what Jon said, if in the below mentioned code, the function call for “MessageBox” were to be replaced with the standard VBA msgbox, it should give the same results.
[
2
3
4
5
6
7
8
9
10
Dim result As Integer
result = MessageBox("hello", vbInformation + vbYesNo, "My title")
Select Case result
Case 1, 4, 5, 6
MessageBox ("you want to continue!")
Case 2, 3, 7
MessageBox ("you want to stop")
End Select
End Sub
… works pretty much the same as…
[
2
3
4
5
6
7
8
9
10
Dim result As Integer
result = MsgBox("hello", vbInformation + vbYesNo, "My title")
Select Case result
Case 1, 4, 5, 6
MsgBox ("you want to continue!")
Case 2, 3, 7
MsgBox ("you want to stop")
End Select
End Sub
Pardon me being naive, did I miss something along the way ?
August 13th, 2009 at 11:07 pmThe purchases I make are eirntely based on these articles.
July 27th, 2011 at 7:07 pm