Blog Archives

Excel VBA Message Boxes (Part 3)

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.

The values for each button are as follows:
Image

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:

Sub RespondToMessage()

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!”

End Select

End Sub

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.

Image

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.

Image

Follow me on Twitter @excelmate

Advertisements

Excel VBA Message Boxes

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 ()

MsgBox “Hello”

End Sub

Image

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:

Image

I’ve left the message in view so you can see where the icon appears

Button choices:

Image

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:

Image

There is also a button4 option but this is used if you add an extra help button

Modality:

This basically how you want the message box to behave.

Image

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

End Sub

This would give us:

Image

Remember that all these are optional parameters, so you can select as many or as few as you like.

Custom title:

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”

End Sub

And this would give us;

Image

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.