How to Use MsgBox and InputBox in VBA Excel

If you practice regularly, developing UserForms isn’t difficult. But using the built-in functions of Excel VBA to perform the same thing is easier. There are two functions MsgBox and InputBox in VBA in Excel. These two functions help us to display simple dialog boxes without creating UserForms in the VBA Editor. We can customize these dialog boxes, but for sure they will not provide all the options we can perform using a UserForm. In this article, we’ll talk about MsgBox and InputBox in VBA in Excel.

Introduction to InputBox() Function

As the name implies, we can easily display an InputBox to the viewers and request responses from them using the VBA InputBox() function. Particularly, this is useful for obtaining a single input from the user.

🔱 Objective:

This function returns an InputBox with some kind of criteria.

Syntax:

The syntax of this function is as follows:

InputBox(Prompt, [Title], [Default], [Xpos], [Ypos], [Helpfile], [Context] )

Parameters or Arguments:

The arguments are defined as follows.

• Prompt: This is the only required argument of this function. This text is displayed in the input box.
• Title: It is optional. This text is displayed in the input box’s title bar.
• Default: It is also optional. The default value showed in the input field.
• Xpos: It is a numeric value that indicates the prompt’s horizontal distance from the left side of the screen (X-axis position); if left empty, the input box will be horizontally centered.
• Ypos: It is an optional argument as well. It tells of the Y-axis positioning location of the InputBox.
• Helpfile: to provide a user-friendly assistance file. By pressing the help button, the person can open that file.
• Context: It indicates the HelpContextId in the ‘Helpfile’ that is being called. It’s an optional parameter. But it becomes required when the ‘helpfile’ argument is given.

How to Use InputBox() Function: 3 Examples

Now, we’ll show some examples to make it clear. So, let’s explore them one by one.

1. Excel VBA InputBox with Multiple Inputs

In our first example, we’ll learn how we can get multiple inputs from users using InputBox. So, let’s follow it.

📌 Steps:

Here, we want to get the username and password in cells C5 and C6. To do this,

• At the very beginning, go to the Developer tab.
• Then, click on Visual Basic on the Code group on the ribbon.

Immediately, the Microsoft Visual Basic for Applications window appears.

• At this time, double-click on Sheet1 on Project Explorer.

Thus, it opens the code module.

• Now, paste the following code into the module.

Sub Mutiple_Input()
Dim user As String
Dim pass As String
user = InputBox("Enter the username:", "Exceldemy", "Enter Here")
pass = InputBox("Enter Password:", , "Keep it Secret")
Range("C5") = user
Range("C6") = pass
End Sub

• After that, run the subprocedure using the Run icon. Otherwise, press F5 on the keyboard to replicate the same task.

After pressing the Run icon, the code gets to execute itself. Instantly, an InputBox appears before us. The title of this dialog box is Exceldemy as we set it in the code.

• Then, write down the username in the blank box as in the following image.
• Next, press OK.

After pressing OK, it seeks the password.

• Presently, enter the password into the box.
• Later, click OK.

Currently, we can see the inputs acquired in the places in cells in the C5:C6 range.

Read More: VBA MsgBox Format in Excel

2. VBA InputBox with Options

Now we’ll create an InputBox that can show multiple options to the users. From the available options, the user can choose their desired one and write it down in the blank box. Here, we’ve to input the name of the student and his corresponding grade in cells B5 and C5.

For this purpose, we’ll use InputBox with options. So, without further delay, let’s dive in!

📌 Steps:

• Firstly, open the code module like in the previous example.
• Secondly, write down the following code into the module.

Sub InputBox_with_Options()
Dim S_name As String, Msg1 As String, Msg2 As String, Msg3 As String, Box_Title As String, Box_Default As String
Dim Grade As String
Msg1 = "Enter the Grade Here!"
Msg2 = "A+ for marks" & vbTab & "over 80%"
Msg3 = "A for marks" & vbTab & "under 80%"
Box_Title = "Exceldemy"
Box_Default = "Do It Carefully"
S_name = InputBox("Enter Name of the Student", Box_Title, Box_Default)
Grade = InputBox(Msg1 & vbCrLf & vbCrLf & Msg2 & vbCrLf & Msg3, Box_Title, Box_Default)
Range("B5").Value = S_name
Range("C5").Value = Grade
End Sub

• Thirdly, run the code just like we did it before.
• After executing it, a dialog box appears before us like in the picture below. The names of the different parts of this box are given in the image. So, check it carefully.

• Then, enter the student’s name in the box.
• As usual, click OK.

• Afterward, another dialog box pops up. Here, we can see options on the box.
• In the blank box, write down the grade of the student according to the options given above.
• As always, click OK.

• Finally, we can see our inputs in the B5:C5 range.

3. Excel VBA InputBox with Drop-Down List

Currently, we’ll insert an InputBox with a drop-down list. Actually, it’s not an InputBox but looks like it. It can get input from users. Here, we got some names of the Class.

From this, we’ll create a box that can get input from a drop-down list of these class names. To do this, we’ll use Combo Box. Let’s see it in action.

📌 Steps:

• Initially, go to the Developer tab.
• Then, click on the Insert drop-down on the Controls group.
• After that, select Combo Box (ActiveX Controls) from the available options.

• Later, draw a box with the cursor as in the following image.
• Then, double-click on the box.

Immediately, it gets us to the Microsoft Visual Basic for Applications window. Also, a code module was created for this Combo Box automatically.

• In Project Explorer, double-click on ThisWorkbook to open a code module with the Workbook event.

• Presently, paste the following code into the new code module.

Private Sub Workbook_Open()
With Worksheets("Drop-down").ComboBox1
.AddItem "Grade 1"
.AddItem "Grade 2"
.AddItem "Grade 3"
.AddItem "Grade 4"
End With
End Sub

• In this instance, save the workbook. Then, close the file and open it again. Thus, you can see the InputBox working like the image below.

Here, you can select any of the four options from the drop-down list.

Introduction to Application.InputBox Method

You also can use the Application.InputBox method to do the same task. But it has some advantages over the InputBox function.

• We can define the resulting datatype.
• You can identify when someone clicks the Cancel button.
• You may quickly determine whether the return value is False by assigning it to a Variant datatype.

🔱 Objective:

The objective of this method is also the same as the previous function. It also returns an InputBox with some advanced features.

Syntax:

The syntax for this method is kinda same as the InputBox function. It’s as follows.

Application.InputBox(Prompt, [Title], [Default], [Left], [Top], [Helpfile], [HelpContextID], [Type])

Parameters:

The arguments of this method are mostly similar to the arguments of the InputBox function. Just there is an extra parameter here that is Type. It’s an optional argument. Actually, it defines the type of returned data. In the absence of this option, the InputBox will just return text as a default. Here is the list of the value of the data types.

How to Use Application.InputBox Method: 2 Examples

Now, we are going to see some examples using this new method. Let’s explore them step by step.

1. InputBox with Multiple Lines

In our first example of this method, we’ll learn how we can make an InputBox with multiple lines. Here, we’ll input the Student’s Name using an InputBox.

For this, we’ll use Application.InputBox method. Let’s go through the steps.

📌 Steps:

• Like before, place the following code into the code module.

Sub InputBox_with_Multiple_Lines()
Dim S_name As String
S_name = Application.InputBox("Enter Name of" & vbNewLine & "the Student", "Exceldemy")
Range("B5").Value = S_name
End Sub

• Subsequently, run the code and you’ll get an InputBox that has two lines as prompt.
• Then, make the entry in the blank box.
• Also, click OK.

Immediately, it gets placed itself into cell B5.

Note: The fact to notice here is that we haven’t specified any data type in our code. So, as default, the InputBox takes the entry of a text string that we’ve addressed before.

2. InputBox with Specific Data Type

In this example, we’ll use the Type argument of this method. At this time, we’ll get input of the score of a student through the InputBox.

So, look at the example very carefully to learn the use of data type.

📌 Steps:

• Similarly, paste the code below into the code module.
• Notice that we’ve used data type 1 in our code. That means our InputBox will allow just numbers as input.

Sub InputBox_with_Specific_Data_Type()
Dim Score As String
Score = Application.InputBox("Enter the Score:", "Exceldemy", Type:=1)
Range("C5").Value = Score
End Sub

• Now, run the code like before.
• Thus, an InputBox appears. Here, input the marks of the student.
• Consequently, click OK.

Presently, the score gets placed into cell C5.

But, what would happen if I didn’t enter a number? Let’s see this phenomenon.

• Later, execute the code again and write A+ in the input box.
• Then, click OK.

Instantly, it will show a warning box like in the following image.

Read More: Excel VBA: Work with Variable in MsgBox

Introduction to VBA MsgBox() Function

The MsgBox function is an easy way to display information and to take simple input (like Yes, No, Cancel) from the users. It appears in the display as a pop-up. For example, we can say Hello through a MsgBox to our users. For this purpose, we’ll use the MsgBox() function.

MsgBox function can be used by itself like this one: MsgBox “Click OK to continue” (parenthesis not included in this way) or it can be assigned to a variable like the above one: Ans = MsgBox(“Process the monthly report?”, vbYesNo).

🔱 Objective:

The MsgBox function shows a message in a dialog box, waits for the viewer to select a button, and then sends back an Integer number according to the clicked button.

Syntax:

A simplified version of the MsgBox syntax is as follows:

MsgBox ( Prompt, [Buttons], [Title] )

Parameters:

The arguments are defined as follows:

• Prompt: It is required. This text is displayed in the message box.
• Buttons: It is optional. Here we shall use some code such as vbYesNo, vbOKOnly in this place. According to this code, different buttons will appear in the message box.
• Title: It is optional. This text appears in the message box’s title bar.

MsgBox Table: Constants Used in the MsgBox() Function

Just have a look at the following table to grasp the used constants in the MsgBox function.

2 Examples of Using MsgBox() Function

Here, we’ll show 2 robust examples using the Msgbox function in Excel VBA. Let’s see these in detail.

1. MsgBox with Different Buttons

In this example, we’ll show how to create a message box with different buttons. Follow the steps below.

📌 Steps:

• Equivalently, paste the code into the code module.

Sub Get_Answer()
Ans = MsgBox("Process the monthly report?", vbYesNo)
If Ans = vbYes Then MsgBox ("Some reports will be showed :)")
If Ans = vbNo Then Exit Sub
End Sub

• Hereafter, run the code like before.

Instantly, a MsgBox will appear on the display.

• To bring the next box, click on the Yes button.

Immediately, it shows another MsgBox with some information.

• Here, click OK.

Read More: Create VBA MsgBox Custom Buttons in Excel

2. MsgBox with Different Icons

Now, we have used a combination of constants (vbYesNo, vbQuestion, and vbDefaultButton2) to show a dialog box with a Yes button, a No button, and a Question mark icon. The default button (vbDefaultButton2) will be the second button (No button). So, follow the example below.

📌 Steps:

• Alike, bring the code into the module.

Sub GetAnswer()
Config = vbYesNo + vbQuestion + vbDefaultButton2
Ans = MsgBox("Process the monthly report?", Config)
If Ans = vbYes Then MsgBox "You pressed to see the Monthly Report.", vbInformation
If Ans = vbNo Then Exit Sub
End Sub

After executing the code, we can see the MsgBox with a question mark.

• Then, click Yes to proceed.

In the next step, we can see another Information icon on the prompt.

• Then, click OK to close the MsgBox.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

This article provides easy and brief solutions for MsgBox and InputBox of VBA in Excel. Now, you can easily use MsgBox and InputBox in VBA in your Excel file. Don’t forget to download the practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Anyway, happy Excelling! 🍵

Related Article

• How to Use Excel VBA MsgBox Title
• Excel VBA: Show Multiple Values with Multiple Variables in MsgBox
• VBA MsgBox to Return Values in Excel
• Excel VBA: Modeless MsgBox