How to Use MsgBox & InputBox in VBA for Better Interaction

Capturing User Responses with MsgBox using If… Else

In some cases, we need to capture the user’s preferences from MsgBox to determine the next step in the VBA code. This can be done by storing MsgBox results into variables and using If…Else to process user responses.

Examples of using MsgBox to capture user responses:

Dim response As Integer
response = MsgBox(“Are you sure you want to continue?”, vbYesNo + vbQuestion, “Confirm”)

If response = vbYes Then
    MsgBox “You chose Yes!”, vbInformation, “Result”
Else
    MsgBox “You chose No!”, vbExclamation, “Result”
End If

Getting to Know the Input Box in VBA

Input Box (InputBox) is a feature in VBA (Visual Basic for Applications) that allows users to enter data in the form of text or numbers through a simple dialog window. InputBox is often used to request information from users, such as names, numbers, dates, or other data required in VBA processing.

Basic Syntax of InputBox

The basic syntax for displaying InputBox in VBA is as follows:

Dim userName As String
userName = InputBox(“Enter your name:”, “Input Data”)
MsgBox “Hello, “ &  userName  & “!”

Code explanation:

1. InputBox will display a dialog with the text “Enter your name:”.
2. The data that the user types will be stored in the userName variable.
3. MsgBox will display the greeting “Hello, [name inputd]!” after the user presses OK.

Specifying the Default Value in the InputBox

You can set the default value in InputBox to provide an example of the expected input.

Example code with default values:

Dim city As String
city = InputBox(“Enter your city name:”“, “Input Data”, “Jakarta”)
MsgBox “You live in “ &  city  & “!”

Explanation:

• “Jakarta” will appear as the default text in the InputBox.
• Users can edit or directly press OK to use the default value.

Input Validation from the InputBox to Ensure the Input Data is Correct

Since InputBox only receives input in the form of text, it is important to validate that the data entered is as expected.

Example of validation of inputs so that they are not empty:

Dim userInput As String
userInput = InputBox(“Enter your email:”, “Input Email”)

If userInput = ““ Then
            MsgBox “Input cannot be empty!”, vbExclamation, “Warning”
Else
            MsgBox “The email you entered: “ & userInput, vbInformation, “Confirmation”
End If

Explanation:

• If the user presses OK without filling in the data, a warning will appear.
• If there is a valid input, a confirmation will appear with the entered data.

An example of input validation only accepts numbers:

Dim age As String
age = InputBox(“Enter your age:”, “Input Age”)

If IsNumeric(age) Then
            MsgBox “Your age is “ & age & “ years.”, vbInformation, “Confirm”
Else
            MsgBox “Please enter a valid number!”, vbCritical, “Error”
End If

Explanation:

• IsNumeric() is used to check whether the input is a number.
• If it is not a number, an error warning will appear.

Comparing MsgBox and InputBox: When to Use It?

In VBA (Visual Basic for Applications), both Message Box (MsgBox) and Input Box (InputBox) are used to interact with users, but they have differences in their functions and how they are used. MsgBox focuses more on providing information and getting responses in the form of button choices, while InputBox is used to ask for input directly from users.

Here is a comparison between MsgBox and InputBox based on their key features:

When to Use MsgBox?

Use MsgBox when you want to:

• Displays alerts or information to users.
• Ask for action confirmation before running the process.
• Provides a choice between Yes/No, OK/Cancel, or other buttons.

When to Use InputBox?

Use InputBox when you want to:

• Asks users to enter text, numbers, or other necessary data.
• Use user input as a parameter in the VBA process.
• Allows users to fill out forms or perform searches.