Monthly Archives: September 2012

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