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.


Download Practice Workbook

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


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.

msgbox inputbox vba excel

🔱 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.

3 Examples of Using InputBox() Function

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.

Excel VBA InputBox with Multiple Inputs

Immediately, the Microsoft Visual Basic for Applications window appears.

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

Using 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

Pasting Code in Module

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

Executing Code to Create Excel VBA InputBox with Multiple Inputs

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.

Make first Entry

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.

Excel VBA InputBox with Multiple Inputs


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.

VBA InputBox with Options

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

Writing the Code into Module

  • 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.

Different Elements of InputBox

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

Entering Name

  • 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.

VBA InputBox with Options

  • 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.

Excel VBA InputBox with Drop-Down List

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.

Inserting Combo Box

  • 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.

Workbook Event Handler

  • 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.

Excel VBA InputBox with Drop-Down List

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.

Introduction to Application.InputBox Method

🔱 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.

Value of Data Type Description of Input Data Type
0 Formula
1 Number
2 Text String
4 Boolean (True / False)
8 Range / Cell Reference
16 #N/A
64 Array

2 Examples of Using Application.InputBox Method

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.

InputBox with Multiple Lines

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

Relevant Code

  • 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.

InputBox with Multiple Lines

Immediately, it gets placed itself into cell B5.

InputBox with Multiple Lines

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.

InputBox with Specific Data Type

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

Relevant Code to Create InputBox with Specified Data Type

  • 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.

InputBox with Specific Data Type

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.

Showing Warning Box


Introduction to VBA MsgBox() Function

The MsgBox 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.

Introduction to VBA 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.

Constant Value Description
vbOKOnly 0 We can see the OK button.
vbOKCancel 1 It shows two buttons: OK and Cancel.
vbAbortRetryIgnore 2 It exhibits three buttons: Abort, Retry, and Ignore.
vbYesNoCancel 3 It shows the Yes, No, and Cancel buttons.
vbYesNo 4 We can watch two buttons: Yes and No.
vbRetryCancel 5 It pomps Retry and Cancel knobs.
vbCritical 16 It shows the Critical Message icon.
vbQuestion 32 It showcases a Query icon (a question mark).
VBExclamation 48 We can observe the Warning Message icon.
vbInformation 64 It shows the Information Message icon.
vbDefaultButton1 0 The first button is the default.
vbDefaultButton2 256 The second button is the default.
vbDefaultButton3 512 The third button is the default.

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:

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

MsgBox with Different Buttons

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.

MsgBox with Different Buttons


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

MsgBox with Different Icons

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

  • Then, click Yes to proceed.

Question Mark Icon on MsgBox

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

  • Then, click OK to close the MsgBox.

Information Icon on MsgBox in VBA Excel


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. Please visit our website, Exceldemy to explore more. Anyway, happy Excelling! 🍵

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo