Excel VBA message boxes

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.

...join the conversation...

Your email address will not be published. Required fields are marked *