Excel VBA: Custom Input Box (7 Examples)

VBA Custom Input Box is a type of dialog box that can be created in the Visual Basic for Applications (VBA) window within Microsoft Excel and other applications. It allows the user to prompt for input, such as a value or a text string, and then store that input as a variable within your VBA code. The main advantage of using a custom input box over a regular one is that users have more control over its appearance and functionality. Today, in this article, I am sharing with you some examples of VBA custom InputBox with some simple tricks. Stay tuned!

In the following, you will find an overview of VBA custom Input Box in Excel.

How to Launch VBA Editor in Excel

The VBA editor is the environment where you can launch and manage VBA code. Without moving here and there, let’s learn about launching a VBA editor.

• Opening your worksheet, choose the Visual Basic option from the Developer

• Then, select the Module option to place and manage your VBA

Introduction to Excel VBA InputBox

VBA Custom Input Box is a powerful tool that allows you to interact with users and collect input in a more intuitive and efficient way. It is mainly used to display a prompt to the user and collect input from them.

Objective

This InputBox function returns an output with the provided criteria.

Syntax

The syntax of this function is-

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

Output

The Input Box function returns a value entered by the user in the input box. The return value is always a string.

How to Insert InputBox with Excel VBA

In order to insert a custom Input Box with Excel VBA you can use the InputBox function. In some cases, you can also try the Application.InputBox function which is quite similar to InputBox function. The main advantages of the Application.InputBox function is that you can enter variant data types with more features and flexibility.

Suppose we have a dataset including Employee Name, Age, Department, and Total Salary. Now we will use VBA InputBox with customized inputs.

1. Using InputBox Function to Insert Input Box with Excel VBA

Follow the instructions below to learn about applying the InputBox function.

• Open a module and place the following code-

Sub InputBox_function()
Set Rng = Range("B5:B13")
search_text = InputBox("Enter the Name to search for:")
Set Cell_position = Rng.Find(search_text, _
LookIn:=xlValues, LookAt:=xlWhole)
MsgBox "Found the input name " & search_text & _
" at " & Cell_position.Address
End Sub

• After placing the code press the Run

• Now, you will get a MsgBox where you can enter an input. Here I have written Thomas to look for its position in the worksheet.

• Another MsgBox will provide me with the cell reference for the provided input value. Simple isn’t it?

2. Insert VBA Input Box Applying Application.InputBox Method

For more flexibility, you can try the Application.InputBox function.

• In a similar way, open a module and write the below code down-

Sub InputBox_function()
Set Rng = Range("B5:B13")
search_text = Application.InputBox("Enter the Name:")
Set Cell_position = Rng.Find(search_text, _
LookIn:=xlValues, LookAt:=xlWhole)
MsgBox "Found the input name " & search_text & _
" at " & Cell_position.Address
End Sub

• Hence, press F5 key from the keyboard to Run the code.

• Thereafter, the InputBox function will pop up with the provided string with the help of a MsgBox.
• Simply, put any name and hit OK.

• Finally, we will get our final MsgBox according to the input text.

Excel VBA: Custom Input Box (7 Examples)

I have described 7 simple examples of Excel VBA custom InputBox.

1. Customizing Input Box with Custom Prompt Message

Overview of customizing InputBox with custom prompt message

According to the syntax of InputBox, customizing prompt messages is a simple task. Just put the string inside an astrological sign to get an InputBox with the provided string.

Steps:

• Opening a new module put the below code and run it by hitting the Run icon-

Sub customize_prompt_message_inputbox()
lookup_text = InputBox("Enter the employee name:")
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 4, False)
MsgBox "The employee salary is: " & "$" & result
End Sub

• After that, an InputBox will pop up according to the provided string where we will put a name as input and press OK.

• Finally, we have the salary of the provided employee name. It’s that simple.

Read More: Excel VBA: InputBox Type with Examples

2. Editing InputBox Title to Custom Input Box

In some situations, you can edit the InputBox title for your own customization work. For that,

Steps:

• Insert a module and write the below code down-

Sub customize_prompt_message_inputbox()
lookup_text = InputBox("Enter the employee name:", _
"Name Input")
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 4, False)
MsgBox "The employee salary is: " & "$" & result
End Sub

• Hence, click the Run icon or hit the F5 key from the keyboard to run.

• As a result, we have our own title for the InputBox named Name Input.
• Now, let’s put a name and click OK.

• Within a glimpse of an eye, we have the employee salary for the given name.

3. Customizing VBA Input Box with Default Value

Sometimes, you may want to provide a default value in the InputBox, so that the user can accept the default value or enter a different value. This can be useful for prompting the user for input in a consistent way, while still allowing for flexibility.

Steps:

• Insert a new module, place the below code, and Run

Sub InputBox_Default_Value()
lookup_text = InputBox("Enter the employee name:", _
"Name Input", "Insert Name")
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 3, False)
MsgBox "The employee department is: " & result
End Sub

• As you can see, we have a default value inside the InputBox.

• Let’s put a name inside the box and select OK.

• In conclusion, the final output will be in our hands.

Read More: Excel VBA: InputBox with Default Value

4. VBA Input Box with Custom Positions

In VBA, an InputBox is a function that allows you to prompt the user for input by displaying a dialog box on the screen. By default, the InputBox appears in the center of the screen, but you can customize the position of the dialog box by specifying the X and Y coordinates.

Steps:

• Following the previous method, open a new module and put the below code down, and run it.

Sub InputBox_Custom_Positions()
lookup_text = InputBox("Enter the employee name:", _
"Name Input", "Insert Name", 1800, 3000)
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 2, False)
MsgBox "The employee age is: " & result
End Sub

• Here we have the InputBox placed according to the given coordinates.

• To check whether the code is working or not let’s put a name and click OK.

• In summary, we have the final result for the given name.

5. Adding Helpfile to Create Custom VBA InputBox

The InputBox has limited options for customizing the appearance and behavior of the dialog box. One of the advanced features you can add to a custom InputBox is a Helpfile. A Helpfile is a separate document that provides additional information to the user.

Steps:

• Inserting a module, write the below code and hit the Run

Sub InputBox_helpfile()
lookup_text = InputBox("Enter the employee name:", _
"Employee Information", "Insert Name", , , _
helpFilePath = "E:\Employee Data", HelpContextID = 10)
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 3, False)
MsgBox "The employee department is: " & result
End Sub

• SImply, click the Help button to get instructions for filling out the input fields.

• Immediately a tab will be opened in your browser regarding help.

6. Inserting VBA Input Box That Takes Specific Data Type

When using an InputBox, it is often helpful to specify the data type of the input that is expected, such as a number, date, or string. By specifying the data type of the input, the program can validate the user’s input and ensure that it is in the correct format. This can help prevent errors or unexpected results when the input is used later in the program. Check out the list box below to learn about various specific data types.

In this below part, let’s learn about some specific data type where first we will get an error due to putting text input as we have defined numeric data type in the code.

Example 1: Take Numeric Value Only

• Similar to the previous method, we will open a new module and type the code with a numeric value acceptable data type which is 1.

Sub InputBox_Specific_Data()
input_value = Application.InputBox("Insert William’s Salary:", _
"User Information", , , , , , 1)
End Sub

• Press the F5 key to run the code.

• Next, we will get an InputBox where we will put a text value to check whether it’s working or not.

• As you can see, we have a warning message as we have inserted text value.

• Now, if we put a numeric value the code will run appropriately.

Read More: Excel VBA InputBox with Number Only Criteria

Example 2: Take Only Strings

• In this next step, let’s find out the employee name and so change the data type to 2 and run the code by clicking the Run

Sub InputBox_Specific_Data()
input_value = Application.InputBox("Insert employee name:", _
"User Information", , , , , , 2)
MsgBox "The employee name is: " & input_value
End Sub

• Therefore, put a name and press OK.

• Finally, we have the expected output in our hands with the proper specific data type.

7. Customizing Input Box with Multiple Lines

If you want you can also customize your InputBox with linefeed and carriage return. Please check the below table where you will find multiple constants for your linefeed and carriage return.

In the following, we will learn about vbNewLine constant to add a new line just below the prompt line.

Steps:

• Inserting a module write the code below and run it.

Sub InputBox_Multiple_Line()
lookup_text = InputBox("Enter the employee name:" & vbNewLine & _
"(From the employee records in the worksheet)")
Set Rng = Range("B5:E14")
result = WorksheetFunction.VLookup(lookup_text, Rng, 2, False)
MsgBox "The employee age is: " & result
End Sub

• As a result, we will get a new line just below the prompt string.
• Hence, we will put a name inside the box and press OK.

• Finally, we have the exact output for the given name inside the InputBox. Simple isn’t it?

Read More: Vbscript InputBox with Multiple Fields in Excel

How to Create a Custom Input Box with Multiple Inputs in Excel VBA

Step 1: Creating UserForm Window

First, launch the Visual Basic window >> click  Insert tab >> select UserForm.

• Insert Labels using the ToolBox and change the name of Labels using the Caption option from the Properties window.

• Change the Font style and Size according to your choice.
• Hence, position the Labels by changing the Height, Left, and Width values to 20, 30, and 80.
• Selecting all the Labels change the BackColor to light green using the BackColor code- &H0080FF80&.

• Insert 3 TextBox and 1 ComboBox for collecting values.
• Then, place them inside a Frame and name it Employee Form.

• Now, let’s create 3 CommandButton for submitting, resetting and canceling input values.
• Then, in a new worksheet define the name as Department for the department name and combine it using the RowSource option.

Step 2: Writing Code and Running UserForm

• Hence, put the below code for all three CommandButtons.

Submit Button:

Private Sub CommandButton1_Click()
Worksheets("MultipleInputs").Activate
i = 1
While Range("B4").Cells(i, 1) <> ""
i = i + 1
Wend
MsgBox i
Worksheets("MultipleInputs").Range("B4").Cells(i, 1) = UserForm1.TextBox4.Text
Worksheets("MultipleInputs").Range("C4").Cells(i, 1) = UserForm1.TextBox5.Text
Worksheets("MultipleInputs").Range("D4").Cells(i, 1) = UserForm1.ComboBox2.Value
Worksheets("MultipleInputs").Range("E4").Cells(i, 1) = UserForm1.TextBox6.Text
End Sub

Reset Button:

Private Sub CommandButton2_Click()
TextBox4.Value = ""
TextBox5.Value = ""
ComboBox2.Value = ""
TextBox6.Value = ""
End Sub

Cancel Button:

Private Sub CommandButton3_Click()
Unload Me
End Sub

• Finally, run the UserForm and we have successfully created a custom InputBox with multiple inputs.

How to Check If Cancel Button Was Clicked in Excel VBA Input Box

The InputBox can be configured to include a Cancel button, which allows the user to cancel the input process if necessary. If the user clicks the Cancel button, the InputBox returns a null value or the provided string included for the button. To check if the Cancel button was clicked in an Excel VBA InputBox, you can store the result of the InputBox in a variable.

1. Employing Input Box Function

In this part, we will design a flexible code where a Cancel button will be included. Besides the null result, we will put a message if the user clicks the Cancel button.

Steps:

• Simple, put the code and run it by hitting the Run

Sub InputBox_Cancel_button()
Input_Name = InputBox("Insert Employee name", _
"Employee Information")
If Input_Name = cancel Then
MsgBox "You clicked Cancel."
Else
MsgBox "You entered: " & Input_Name
End If
End Sub 

• After the InputBox appears press Cancel.

• Within seconds, a MsgBox will appear confirming the user clicked the Cancel button.

2. Using Application.InputBox Method Without Selecting Cell Range

You might also need to design an InputBox where you can select a cell range from your Excel workbook. Application.InputBox function is a must for selecting cell range.

Steps:

• Place the below code and Run

Sub inputbox_cell_range()
Dim cell_range As Range
On Error Resume Next
Set cell_range = Application.InputBox("Select a range", Type:=8)
On Error GoTo 0
If cell_range Is Nothing Then
MsgBox "You clicked cancel."
Else
MsgBox "Your selected range is: " & cell_range.Address & "."
End If
End Sub

• Thus, an InputBox will appear where you can choose your desired cell range.

• A MsgBox with chosen cell range will be displayed.

• Without selecting the cell range let’s click the Cancel button.

• Confirmation of pressing the Cancel button will be visualized by a MsgBox.

Things to Remember

• The application method is mostly used to set the input data type. If it is not used then try to provide a specific data type.
• Like other functions InputBox can accept up to 255 arguments.

Frequently Asked Questions

• What are the pros and cons of using Excel VBA Custom InputBox?

Custom InputBox in VBA offers several advantages, such as improved user experience, data accuracy, and customization options. However, there are also potential drawbacks, such as confusing interfaces, time-consuming development, restrictive validation checks, and compatibility limitations.

• How do I retrieve the input values from a custom InputBox?

To retrieve the input values from a custom InputBox, you need to reference the values of the controls on the form. For example, if you have a text box for the user to enter their name, you can retrieve the value of the text box using the Text property.

• How do I handle validation and error checking in a custom InputBox?

You can handle validation and error checking in a custom InputBox by using conditional statements and an error handling code. For example, you can check if the user has entered a valid email address before allowing them to submit the form, or you can display an error message if the user leaves a required field blank.

Download Practice Workbook

You can download our practice workbook from here for free.

Conclusion

In conclusion, a custom Input Box in Excel VBA is a powerful tool that can provide a more efficient and user-friendly input process for users. It offers many benefits such as customization, validation, and reusability but it also comes with its own set of challenges.

Whether or not to use a custom InputBox depends on the specific requirements of your work. It is important to weigh the benefits against the potential drawbacks to determine whether it is the right choice for your task. Take a tour of the practice workbook and download the file to practice by yourself.

Hope this article will help you to create VBA custom Input Box successfully. Please inform us in the comment section about your experience.

Related Articles

• VBA InputBox for Integer Value Only in Excel
• Excel VBA: InputBox Date Format