Posted by excelmate
In my earlier blogs we looked at how to customise the overall appearance of a message box and then on how to customise the message itself. This time let’s look at how we can interact with the message box by performing different actions based on the button selected.
When you first set up a message box with multiple buttons, whichever one you click on will do exactly the same i.e. close the message box. This of course is of no use to anyone.
Each button, however, has a value assigned to it (or 2 to be precise). This can be a system constant or a numerical value.
I have not added the ‘OK only’ option for obvious reasons!
Once we know that each button has a value, we can use this to write code so that if the user selects ‘Yes’ we can make it do one thing and something else if they select ‘No’.
In order to store the value of the selected button we need to create a variable, making sure that the variable type matches the value you are storing. In this example I am going to use byte as my data type, as we are dealing with whole numbers with a maximum value of 7 (well below the 255 maximum a byte can handle).
So our code would look something like this:
Dim Reply as Byte
Reply = MsgBox (“Please select a button”, vbYesNoCancel + vbQuestion _ ,“Respond”)
Select Case Reply
Case Is = 6
MsgBox “You selected ‘Yes’”, , “Well done!”
Case Is = 7
MsgBox “You selected ‘No’”, , “Well done!”
Case Is = 2
MsgBox “You selected ‘Cancel’”, , “Well done!”
Ok…not the most useful set of instructions once you’ve clicked on a button but you get the idea. You can make the response to any button as simple or as complex as you like.
Note: when we state what the variable is equal to we have to add brackets around everything that goes into the message box, including any options and custom titles. If you don’t do this you will be prompted with an error message.
In this example I have used Select Case but you could equally use an If Then Else/If ElseIf Then type statements.
This is a great way to make your macros more interactive by giving the user options as the code runs; Do you want to print the modified sheet? Have you checked the printer for letter headed paper? Etc…etc…the possibilities are endless.
Follow me on Twitter @excelmate
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.