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

Posted on September 18, 2012, in VBA and tagged , , . Bookmark the permalink. 2 Comments.

  1. Hi Richard, a pleasure to see someone appreciating the simple Excel MsgBox (I see I am not the only one). Many VBA beginners simply meddle with UserForms not knowing how well the VBA MsgBox can be configured.

    The MsgBox can be more configured (alignment etc.) not sure you are aware – I also expanded on this topic some time ago if you would be interested:
    http://www.analystcave.com/excel-vba-tutorial/#MessageBox_and_User_Forms

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: