How to display message and input boxes in VBA?

Information Sharing

This part will go through some practical ways to share the information to the coder or user without using the worksheet.

There are 3 ways common ways in order of popularity.

1. Message boxes

2. Immediate Window

3. Input Boxes
   

Message boxes

Message boxes are the most common ways to share some information from the coder to the user or coder himself/herself. It is exactly what it sounds like. A small window pop up displaying some information and halting the code from being run at the same time. Typical placement could be after the script has finished to let the user know he/she can proceed. We have a few variants of message boxes and style for them that could be useful to display for example the urgency of a script, or catching some user input on something.

The following is the set up for a message box where the hard brackets are optional customization: Personally I have only used Prompt, Buttons, and Title argument as I believe those are the needed ones. Helpfile and context creates a popup of the help functions and context to a specific place. MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )

Prompt: "This is your text with these annoying quotation marks" Buttons: This argument allows you to customize the style of the box as well as what buttons it should have to catch any user input needed for the script. Ok button is always default if this argument is omitted. Note that a Style and a Button option can be paired up together for the messagebox with a simple plus sign '+'.

Button options

vbOK = OK button
vbCancel = Cancel button
vbAbort = Abort button
vbRetry = Retry button
vbIgnore = Ignore button
vbYes = Yes button
vbNo = No button

Message style options

vbCritical = Critical message icon
vbQuestion = Question icon
vbExclamation = Waning message icon
vbInformation = Information icon

Title

This argument allows you to customize the title of the message box. If omitted it will just show the standard “Microsoft Excel”.

Standard Message box

Sub Standard_msg()
     MsgBox "Hello Analyst!"
End Sub

Ok + Cancel Message box

Sub Cancel_msg()

'Shows Ok and cancel.
UserInput = MsgBox("Do you want to run the code?", vbOKCancel)

If UserInput = vbCancel Then

        Debug.Print "Don't run"
    Else
        Debug.Print "run code"

End If
 
End Sub

Ok + Retry Message box

Sub Retry_msg()

'Shows Ok and Retry.
UserInput = MsgBox("Do you want to run the code?", vbRetryCancel)
If UserInput = vbRetry Then
        Debug.Print "Don't run"
    Else
        Debug.Print "run code"
End If
 
End Sub

Information style Message box with Title

Sub Info_types()

'Title is Your Boss
'Information symbol is being shown on the message box.
MsgBox "Please analyze faster", vbInformation, "Your Boss"

End Sub

Critical style, YesNo Message box with Title

Sub Critical_types()

'Just add '+' sign to configure YesNo as well as the style of message box to be displayed.
MsgBox "The CFA needs to be on my desk by 8am", vbCritical + vbYesNo, "Your Boss"

End Sub

Exclamation style Message box with Title

Sub Exclamation_types()

MsgBox "Get me coffe", vbExclamation, "Your Boss"

End Sub

Question style Message box with Title

Sub Question_types()

MsgBox "What WACC did you use?", vbQuestion, "Your Boss"

End Sub

Question YesNo style Message box with Title

Sub MsgBoxInformationIcon()

'Display a title do your msbox.
MsgBox "Do you want to continue?", vbYesNo + vbQuestion, "Step 1 of 3"

End Sub

Immediate Window

The Immediate window is a popular way for the coder to see so that the code runs as it should. Whilst a message box often serves this function as well, using the immediate window is a lot more comfortable when displaying a lot more information. Not having to press the Enter key to get rid of the message box when your code is in a loop of 100 times to print out an array or testing an IF statement.. (yeahh we've all been there. don't worry).

Input Boxes

Input boxes are a great way to allow the user to input values or text to customize the code run depending on the input. Alternatively you could dedicate specific cells in your worksheet that are being picked up during the code run to allow the end user to have some input in what is being run. Utilizing input boxes however, makes your code less dependent of specific cells and thus more stable for manipulation.

InputBox

InputBoxes can be used via the function InputBox() or via the Application.InputBox. InputBox Function: Text input from the User. Application Inputbox: Here you can specify the type of required input that is necessary in terms of variables (string, number, range etc. InputBox Function example:

Sub Inputs()

'Input from User that is being stored in MyInput variable
MyInput = InputBox("Please Input your WACC as a percentage", "Wacc Simulation", 5)

'Prints out the answer from the user to the immediate window.
Debug.Print MyInput

End Sub

Application Inputbox example: The below specifies what to of input the sought after.

0   = A Formula
1   = A Number
2   = Text (a string)
4   = A logical value (True or False)
8   = A cell reference, as a Range object
16  = An error value, such as #N/A
64  = An array of values

Sub InputBoxRange()

Set myCell = Application.InputBox(prompt:="Select a cell", Type:=8)

End Sub

Sub InputBoxNr()

Dim MyInput As Double

MyInput = Application.InputBox(prompt:="Enter a number", Type:=1)

'Prints out the result to the immediate window. 
Debug.Print MyInput

End Sub

Sub InputBoxText()

'Note that this converts everything to a text meaning characters and decimals comes in as text.
'Need to create a loop for only textcharacters

Dim MyInput As String

MyInput = Application.InputBox(prompt:="Enter a text", Type:=2)

Debug.Print MyInput

End Sub

Conclusion

If you only need a textinput the Inputboxfunction works just fine. If you need something more refined it could be good to use the Applicaiton.Inputbox function. However, test your code with your inputs to make sure it is bulletproof. For example if you are using the type Boolean, note that all text converts to a True value. Not maybe what you are after. Hence, unless requiring for example a Number or Range which are great functions, confirm that the user input the information as you wish with for example a loop that validates the answer. I set up something quick to illustrate below, however, you will probably use a while loop instead.

Sub InputBoxNrLoop()

For i = 1 To 10

 'Assigns the input value to MyInput
 MyInput = Application.InputBox(prompt:="Enter a number")
 
 'Testing the input variable "MyInput" to make sure it is of numeric 
  type.
 
 'If the supplier value is numeric, the immediate window will print it 
  out.
   
 'Otherwise a "Canceled" text is printed out instead.
 'The syntax Exit For is a great way to exit a for loop rather than 
  using errorhandling measures.

        If IsNumeric(MyInput) Then

            Debug.Print MyInput
            Exit For 'exits the loop

        ElseIf MyInput = "False" Then

            Debug.Print "Canceled"
            Exit For 'exits the loop

        End If
 
Next i

End Sub