Excel VBA Message Boxes
Posted by excelmate
Message boxes can be useful little things when running VBA code. They can be a simple message to let users know something has happened or allows them to make choices as the code runs.
Why the need to let people know something has run? Surely they are not that stupid? Well….you’d be surprised. Code will run very quickly and anyone not familiar with VBA may not notice something has run in the background so will press the button over and over again and before you know it you have 10 copies of the same workbook! So avoid this sort of problem and let people know what has happened.
So how do you set up a message box?
At its most basic all you need is this;
Sub ShowMessage ()
By default you get the OK button to close the message box. So if all you need is a simple message to pop up then that is all you need: MsgBox “prompt or message to the user”, but you can do a lot more…
There are 5 things you can set to customise your message box a bit:
- Message box icon
- Buttons displayed
- Which button is the default one
- Modality (I’ll explain in a bit what that is)
- Custom title
Message box icons:
I’ve left the message in view so you can see where the icon appears
In each case note that the default button is always the first one on the left (bold outline).
To make buttons respond differently you need to write code to respond to the choice of button. I’ll write something on that in a separate blog.
Setting the default button:
There is also a button4 option but this is used if you add an extra help button
This basically how you want the message box to behave.
Putting it together….
After typing in your message or prompt to the user type in a COMMA and this will bring up all the options for the second parameter. Select as many as you need, with a ‘+’ symbol between each one. If you type in another comma, you will move on to the next parameter which is the message box title. A completed example might look like this;
Sub ShowCustomMessage ()
MsgBox “Hello”, vbInformation + vbYesNo + vbDefaultButton2 + vbApplicationModal
This would give us:
Remember that all these are optional parameters, so you can select as many or as few as you like.
Once you have selected all your options, type in a COMMA and this will give you the option to customise the message box title, replacing the standard Microsoft Excel text you normally see.
Sub ShowCustomMessage ()
MsgBox “Hello”, vbInformation + vbYesNo + vbDefaultButton2 + vbApplicationModal,”Greetings”
And this would give us;
So that is how to set up a message box using VBA. In the next blog I’ll show how to customise the text that appears including custom data formats and line breaks.