We’ve just started looking at dialog boxes and the message box that we’ve had in front of us has been about the simplest we can create. You can do more with message boxes. You can create more complicated message boxes with different buttons that allow users to make different choices and send themselves to different branches of a program.
A message box with multiple buttons
Message boxes with multiple buttons
Here’s the code for a more complicated message box with yes/ no buttons:
Answer = MsgBox ("As well as IRR do you want your returns expressed as a money multiple? ", vbYesNo)
We’re going to create a more complicated version of the macro we were playing with when we first got into dialog boxes. This version’s going to use a message box to ask the user whether they would like returns expressed in a different way. Then the macro’s outputs will change accordingly.
An example for download
Example code for a more complicated message box
You can download the example here: complex message box. The effect of clicking “yes” on the new message box is to send the program off to calculate returns in a different way (as a money multiple). If the user clicks “no” this is all bypassed.
Sub IRRMacro() 'This macro calculates the returns of an investment as an IRR and, if the user wants it, as a money multiple 'Establish variables Dim MoneyIn As Double Dim MoneyOut As Double Dim Years As Double Dim InternalRateOfReturn As Variant Dim Answer As Integer Dim MoneyMultiple As Double 'Create a location for the later GoTo statement EnterMoneyIn: 'Collect up inputs MoneyIn = InputBox("Enter the amount of money you are putting in to the investment: ") MoneyOut = InputBox("Enter the amount of money you expect to get out at the end of the investment: ") Years = InputBox("Enter the time-frame for your investment (in years): ") 'Check whether any of the inputs are less than 0 If MoneyIn < 0 Or MoneyOut < 0 Or Years < 0 Then 'Notify the user that they have entered invalid values MsgBox ("All your inputs need to be +ve numbers - please start again ") 'Use GoTo statement to send the user back to the beginning GoTo EnterMoneyIn 'The next two lines could be deleted and the macro would still work fine Else MsgBox ("Ooo that's a lot of money you're putting in - do you'll think you'll be happy with the expected returns? ") 'Close If statement End If 'Ask the user whether they would like returns output as a money multiple as well Answer = MsgBox("As well as IRR do you want your returns expressed as a money multiple? ", vbYesNo) 'Applies if user wants returns expressed as money multiple If Answer = vbYes Then 'Calculate money multiple MoneyMultiple = (MoneyOut / MoneyIn) 'Format money multiple to 1 decimal place MoneyMultiple = Format(MoneyMultiple, "#,##0.0") 'Output the result in a message box MsgBox "The money multiple for your investment is " & MoneyMultiple 'The next two lines could be deleted Else MsgBox ("OK then - all we'll calculate is the IRR for you ") 'Close If statement End If 'Calculate IRR of investment InternalRateOfReturn = (MoneyOut / MoneyIn) ^ (1 / Years) - 1 'Format IRR as % to 1 decimal place InternalRateOfReturn = FormatPercent(InternalRateOfReturn, 1) 'Output the result in a message box MsgBox "The IRR of your investment is " & InternalRateOfReturn End Sub
Other kinds of message box
There are lots of different types of message box
There are lots of message boxes you can use without getting into creating your own user forms. Here’s a list straight from Excel’s VBA help function (“Alt F11” to bring up the VBA editor followed by “F1”). Good on you Microsoft!
Adding a title to your message box
Syntax for the message box
Excel VBA help also gives us some information on the syntax for the message box (get used to this, as you progress you’re going to want to use that help function a lot – it’s a goldmine of information):
What’s presented above is a bit like the structure we’re used to seeing in standard Excel formulas isn’t it? We’ve been using the first two “arguments” in our message box above. The rest are unlikely to be of interest to you – although you might want to use add a “title” to your message box:
Answer = MsgBox ("As well as IRR do you want your returns expressed as a money multiple? ", vbYesNo, "Aren't message boxes cool? ")
We’re not sure there’s much more we can say about message boxes!
About this online Excel course material
You’re looking at a free online course extract covering different kinds of Excel macro dialog boxes.