Tag Archives: InputBox

MsgBox vs InputBox vs UserForm

VBA MsgBox vs InputBox vs UserForm

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and form needs to have that personal touch. The effect is often the opposite – with users being confused or experiencing errors with custom UserForms without proper error handling.

Today I want to make it easier for all those VBA users out there.

The form/dialog decision tree

Use the guide below when making your decision on whether you need that UserForm or if you are using that Messsage Box or InputBox in the right case:
VBA Message Box is

Decision Tree: MsgBox vs InputBox vs UserForm
Decision Tree: MsgBox vs InputBox vs UserForm

When to use Message Boxes?

Use the VBA MsgBox in the following situations:

  • Short message / information / warning – you want to present the user with a simple message be it an error, message or a warning
  • Yes or No decisions – ask the user if he/she agrees to your questions/ask or if the macro is to proceed etc.

Don’t use the VBA MsgBox in the following situations:

  • Long text messages – when wanting to display long messages or text

When to use the InputBox?

Use the VBA InputBox in the following situations:

  • Simple single variable input – you want the user to input a simple variable such as age, length etc.

Don’t use the VBA InputBox in the following situations:

  • Yes or No / Boolean variables – don’t ask the user to type in yes or no, 1 or 0 or similar boolean variables. Use a MsgBox instead.
  • Enumerations – don’t ask the user to input an item from a discrete list of values. Use a UserForm and a dropdown or listbox instead

When to use the UserForm?

Use the VBA UserForm in the following situations:

  • Complex input / select / interaction – use the UserForm when other options fail and simply a MsgBox or InputBox won’t do the trick

Don’t use the VBA UserForm in the following situations:

  • Simple single variable input – need the user to input an age or length value? Why not use the InputBox instead?
  • Yes or No / Boolean variables – use the MsgBox instead

Agree or disagree? Let me know in your comments!

VBA InputBox

VBA InputBox – How to request user input?

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

The VBA InputBox is a very useful feature along the VBA Message Box, before needing to reach out for VBA UserForms. The InputBox allows you to create a simple pop-up message with a single user input textbox.

InputBox example
InputBox example

A disclaimer to begin with: Refrain from using the InputBox for yes/no, binary questions, as shown above. Use the VBA Message Box instead – configure the buttons to vbYesNo.

InputBox function definition

Let us start with the InputBox function definition:

InputBox( Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context])

As noticed above the InputBox function requires, as a minimum, only the Prompt parameter to execute. What do the parameters stand for?

Prompt
A string explaining what kind of input is needed from the user. This message appears inside the InputBox above the textbox for user input.

Title
Optional. The title of the entire InputBox.

Default
Optional. The default value of the InputBox. This value will appear by default in the user input textbox.

XPos and YPos
Optional. These parameters specify the x and y positions for the input dialog box in relation to the upper-left corner of the screen, in points.

HelpFile
Optional. The name of the Help file for the inputbox. If HelpFile and Context parameters are present, a Help button will appear in the upper-right hand of the dialog box.

Context
Optional. The context ID number of the Help topic in the provided HelpFile.

The VBA InputBox is a modal dialog box. This means it must be closed or hidden before you can continue working in VBA, Excel, PowerPoint or any other application.

The VBA InputBox return the value input by the user in the input textbox.

InputBox examples

Time to explore some code examples

InputBox "How old are you"?

InputBox: How old are you?
InputBox: How old are you?

Now let’s add a Title and a Default value.

InputBox "Shall we continue? (yes/no)", "Continue or end", "yes"

InputBox: Shall we continue?
InputBox: Shall we continue?

Notice that if you want to request specific input from the user you need to suggest (e.g. in brackets) the available options.

Get value provided by user

The function is modal hence prevents further execution until the user provides the required value. Getting the provided value is easy as it is returned by the InputBox function:

Dim val As Long
val = InputBox("How old are you?")

Resolving errors / incorrect input

The VBA InputBox does not prevent the user from providing random / junk values so you need to handle validation yourself. I suggest a simple InputBox validation loop as such:

Dim val As String

Do Until IsNumeric(val)
  val = InputBox("How old are you?")
Loop
'Success
Debug.Print "You are " & val " years old"

Summary

Use the VBA InputBox function instead of custom UserForms whenever possible. Why reinvent the wheel? Similarly you can stretch/customize the VBA Message Box.