Vbscript InputBox with Multiple Fields in Excel (2 Examples)

Creating an InputBox with multiple fields can be a useful technique when building Vbscript programs that require user input for several parameters. An InputBox with multiple fields can help to simplify the user experience by presenting all the necessary input fields in a single dialog box. In this article, we will walk you through the process of creating a VBScript inputbox with multiple fields. We will cover the basic syntax of InputBox, show you how to use variables to store user input and provide you with practical examples that you can use to implement this technique in your own Vbscript programs.

How to Launch UserForm Interface in Excel

To open the VBA code editor in Excel you can utilize the keyboard shortcut. Let’s see the process.

• Press Alt + F11 to open your Microsoft Visual Basic.

• Then, press Insert > UserForm to open a blank UserForm.

Vbscript InputBox with Multiple Fields in Excel: 2 Examples

VBScript is a scripting language commonly used in Windows environments for automating tasks and building simple applications. By using this, we can create input boxes with multiple fields for a variety of use cases such as collecting user data, prompting the user for credentials, or collecting preferences. In this article, we will discuss two practical examples of how to create inputbox with multiple fields using VBScript.

1. Making Multiple InputBoxes in One UserForm Using VBA

When it comes to collecting data in Microsoft Excel, there are times when you need to collect them by using a single input box. Thus you have to open multiple input boxes to collect and assign those data to your Excel sheet. However, having multiple input boxes can clutter your interface and make it difficult to navigate. Fortunately, with the use of UserForm, you can create multiple fields in your input boxes. This allows you to collect all necessary information from the user while maintaining a clean and organized user interface. In this tutorial, we will guide you through the steps of creating multiple input boxes in one UserForm using VBA.

📌 Steps:

• With the help of Toolbox, draw the outline of your UserForm as shown below.
• If the Toolbox window doesn’t open immediately, click View, then Toolbox. Thus your window for the toolbox will open.
• Firstly, use the Frame tool to add a frame to your UserForm.
• Change the caption name from the Properties window to be shown in an appropriate manner. For doing so, click on the right button of the mouse and select the Properties command.

• Add a Label and change the caption accordingly.

• Change the BackColor of your Label from the Properties.

• Add the following Label in the UserFrom in a similar way.

• Add three TextBox by using the Toolbox as shown below.

• Now add a SpinButton which will be used later to add numbers in the corresponding TextBox.

• Add two ComboBox to make a DropDown list later on.

• Lastly, add three CommandButton by which you will Run your VBA code.

• Now double-click on Submit button to assign the VBA code.

• Copy the following VBA code and Paste it into your VBA code.

'Return the spinbutton1 value to textbox2
Private Sub SpinButton1_Change()
TextBox2.Value = SpinButton1.Value
End Sub
'Making Drop-Down list for last two questionnaire
Private Sub UserForm_Initialize()
    ComboBox1.List = Array("Ohio", "Boston")
    ComboBox2.List = Array("Yes", "No")
End Sub
'Assigning the userform value to Excelsheet
Private Sub CommandButton1_Click()
lastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(lastRow, 2).Value = MutipleInputBox.TextBox3.Value
Cells(lastRow, 3).Value = MutipleInputBox.TextBox1.Value
Cells(lastRow, 4).Value = MutipleInputBox.TextBox2.Value
Cells(lastRow, 5).Value = MutipleInputBox.ComboBox1.Value
Cells(lastRow, 6).Value = MutipleInputBox.ComboBox2.Value
End Sub
'Clear the existing value in the inputbox
Private Sub CommandButton2_Click()
    MutipleInputBox.TextBox1.Value = ""
    MutipleInputBox.TextBox2.Value = ""
    MutipleInputBox.TextBox3.Value = ""
    MutipleInputBox.ComboBox1.Value = ""
    MutipleInputBox.ComboBox2.Value = ""
End Sub
'Close the UserForm window
Private Sub CommandButton3_Click()
Unload MutipleInputBox
End Sub

• Now select the Run button or Press on F5 to run your Code as follows:

Read More: VBA InputBox for Integer Value Only in Excel

2. Creating UserForm to Perform Mathematical Calculations with Multiple Input Fields

Another way to make multiple input fields is to create a number of UserForm integrated into a single UserForm window. Then do a mathematical operation, let’s say we want to do the multiplication of two given integer values through the UserForm and VBA commands. Let’s have a detailed overview of how the strategy works.

• First of all, create a UserForm by using the Toolbox in your VBA editor window.
• Here for creating this specific UserForm, we use Commandbutton.

• Then, press Insert > UserForm to open another blank UserForm.
• Later, draw a UserForm to take the value of A as below.

• Similarly, draw another input box UserForm3 to have the value of B.

• Now click on UserForm2 from the Project Window and double-click on Submit & Return button.
• Write the following code in your VBA editor to take the input data and return you to UserForm1 once you click the Submit & Return button.

Private Sub CommandButton1_Click()
UserForm2.Hide
End Sub

• Similarly, do the same for UserFrom3 as well.

Private Sub CommandButton1_Click()
UserForm3.Hide
End Sub

• Now back to UserForm1, and double-click on Calculate button.

• Write the following VBA code in the VBA Editor to accomplish the desired task.

'Integrates Userform2 to CommandButton1
Private Sub CommandButton1_Click()
UserForm2.Show
End Sub
'Integrates Userform3 to CommandButton2
Private Sub CommandButton2_Click()
UserForm3.Show
End Sub
'Execute the Multiplication of two given value for A and B
Private Sub CommandButton3_Click()
Dim product_value As Single
product_value = UserForm2.AValue * UserForm3.Bvalue
MsgBox "The product of the two given value is: " & product_value
End Sub
'Close the UserForm1 Window
Private Sub CommandButton4_Click()
Unload UserForm1
End Sub
'Close the UserForm2 and UserForm3 Window
Private Sub CommandButton5_Click()
Unload UserForm2
Unload UserForm3
End Sub

• After that, press the F5 key to Run the given code and see the output as given below.

Read More: Excel VBA InputBox with Number Only Criteria

How to Design Multiple Choice Input Boxes with Excel VBA

In VBA, a UserForm is a graphical interface used to get input from users or display output to users. Multiple option buttons are a type of control that can be added to a UserForm, allowing users to select one option from a set of predefined choices. When the user selects one option, the other options are automatically deselected. In this way, multiple option buttons can help to streamline user input and ensure accurate data collection.

In this section of our article, we will now make a form for a set of people to investigate their employment status and store the record in the Excel spreadsheet.

• From the Toolbox, select the Label control, TextBox, and draw it on the UserForm.
• Change the “Caption” property of the Label to the text you want to display.

• Add two Frames and change their Caption as given below.

• Now add two ListBox as follows.

• Draw two CommandButton and label them as Submit and Cancel.

• Now it’s time to add the option buttons in the created ListBox of UserForm.
• Double-click on the Submit button to assign the code.

Private Sub CommandButton2_Click()
Unload UserForm5
End Sub
Private Sub CommandButton1_Click()
'get first empty row on sheet (read about it here)
lastRow = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row + 1
'initialize each cell with data
Cells(lastRow, 2).Value = TextBox1.Value 'first cell
Cells(lastRow, 3).Value = TextBox3.Value 'first cell to the right
'checking radio button
If ListBox1.Selected(0) = True Then
    Cells(lastRow, 4).Value = "Full-time" 'second cell to the right
ElseIf ListBox1.Selected(1) = True Then
    Cells(lastRow, 4).Value = "Part-time" 'second cell to the right
ElseIf ListBox1.Selected(2) = True Then
    Cells(lastRow, 4).Value = "Contractual"
Else
    Cells(lastRow, 4).Value = "Unemployed"
End If
If ListBox2.Selected(0) = True Then
    Cells(lastRow, 5).Value = "Male"
Else
    Cells(lastRow, 5).Value = "Female"
End If
End Sub
Private Sub UserForm_Initialize()
    ListBox1.AddItem "Full-time"
    ListBox1.AddItem "Part-time"
    ListBox1.AddItem "Contractual"
    ListBox1.AddItem "Unemployed"
    ListBox2.AddItem "Male"
    ListBox2.AddItem "Female"
End Sub

• Now see the output as given below.

Read More: Excel VBA: Custom Input Box

How to Create InputBox with Multiple Lines Using vbMultiLine Argument with Excel VBA

In Visual Basic for Applications (VBA), the InputBox function is used to prompt the user for input. By default, the InputBox displays a single-line text box where the user can enter a value. However, in some cases, you may need to prompt the user for input that requires multiple lines of text. To do so, you can use the InputBox function with the optional argument vbMultiLine. This argument tells the InputBox function to display a text box that allows the user to enter multiple lines of text.

• Write the following VBA code in your VBA Editor.

Sub Multiline_Inputbox()
    'declare variable to hold value returned by InputBox
    Dim Var As String
    'create InputBox with multiple lines
    Var = InputBox(Prompt:="Create Excel VBA InputBox" & _
    vbNewLine & "with multiple lines")
    'display message box with value held by variable
    MsgBox "Your input was: " & Var
End Sub

• Now see the output as given below. You may notice that the line breaks after the InputBox word which is not seen in default mode.

Frequently Asked Questions

• What is the InputBox () function?

Like many other computer languages, VBScript provides an inbuilt function called InputBox() that shows a dialog box to the user and requests input. The user-inputted value is returned by the function as a string that can be assigned to a variable or utilized in other operations. The dialog box normally has an input field where the user can enter their input and a message to guide them.

• What is the maximum length of InputBox in VBScript?

The length of any environment string provided as a command-line argument is limited to 32768 characters.

• Can an input have multiple values in InputBox?

No.  The InputBox function is designed to accept a single value as input from the user. It is not intended to collect multiple values or parameters from the user in a single input box. However, you can go for different methods like creating a custom user form or dialog box with multiple fields.

• What is the difference between MsgBox () and InputBox ()?

The difference between MsgBox() and InputBox() in VBScript is that MsgBox() is used to display a message to the user, while InputBox() is used to prompt the user for input. MsgBox() does not accept any user input, whereas InputBox() returns the value entered by the user as a string.

• What is the string limit in VBS?

In VBScript, the maximum length of a string is 2147483647 characters. This limit applies to both literals and variables of the String data type. However, in practice, the amount of memory available to store the string can also affect the practical limit on the length of the string.

Download Practice Workbook

You can download and practice the UserForm that we have used to prepare this article.

Conclusion

In conclusion, creating VBscript inputbox with multiple fields in Excel can be a useful way to collect user input for a variety of use cases. These input boxes can be customized to include specific fields, messages, and default values to make the user experience more intuitive. However, there are some disadvantages to using input boxes, such as limited formatting and validation options, and the fact that they can only accept input from the user in string format.

Ultimately, choosing the right input method for a particular use case depends on factors such as the complexity of the input required and the desired user experience. Additionally, if you have any queries relevant to the above-mentioned user case example, feel free to comment below.

Related Articles

• Excel VBA: InputBox Type with Examples
• Excel VBA: InputBox with Default Value
• Excel VBA: InputBox Date Format