Vbscript InputBox with Multiple Fields in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

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

 

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.

Opening of Userform interface

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

Blank UseForm Interface


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.

Frame Drawing using the toolbox and Changing the caption name from the properties window

  • Add a Label and change the caption accordingly.

Label Drawing using toolbox

  • Change the BackColor of your Label from the Properties.

Label Drawing using toolbox

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

Label Drawing using toolbox

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

Textbox Drawing using toolbox

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

SpinButton Drawing using toolbox

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

Combobox Drawing using toolbox

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

Commandbutton Drawing using toolbox

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

Double-clicking on submit button

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

Vba code for Userform window

'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

Code Breakdown

  • The initial private subroutine assigns the value of SpinButton1 to TextBox2 whenever SpinButton1_Change event is triggered.
  • The second code block will create two dropdown lists since it populates ComboBox1 and ComboBox2 with pre-defined lists of values during the initialization of the UserForm.
  • The third code block assigns the values entered in the UserForm to the last empty cell in the worksheet when the Submit button is triggered.
  • The fourth code block assigns the textbox value to blank thus clearing the values in the input fields whenever the Clear button is triggered.
  • CommandButton3 aka Cancel button will unload the UserForm once the job is done.
  • Now select the Run button or Press on F5 to run your Code as follows:

Read More: How to Handle VBA InputBox Cancel Button in Excel (2 Examples)


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

Useroform for doing Multiplication

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.

Userform drawing with toolbox

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

Userform drawing with toolbox

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

Userform drawing with toolbox

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

Assigning vba code into the userform

Private Sub CommandButton1_Click()
UserForm2.Hide
End Sub
  • Similarly, do the same for UserFrom3 as well.

Assigning vba code into the userform

Private Sub CommandButton1_Click()
UserForm3.Hide
End Sub
  • Now back to UserForm1, and double-click on Calculate button.

Double click on Calculate button to assign vba code

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

Assigning vba code into the userform

'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: How to Use Excel VBA InputBox with Example


Similar Readings


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.

input box with multiple option buttons

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

Label and textbox drawing using the toolbox

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

Adding frame by using toolbox

  • Now add two ListBox as follows.

Drawing of listbox using toolbox

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

Commandbutton Drawing using toolbox

Userform drawing with toolbox

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

Double-click on Submit button to assign VBA code

Vba code for the userform

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

Code Breakdown

  • The first Subroutine code defines a procedure that is executed when the “Cancel” button (named “CommandButton2“) is clicked. The procedure unloads the UserForm5, which closes the form and returns to the worksheet.
  • The second Subroutine defines a procedure that is executed when the “Submit” button (named “CommandButton1“) is clicked. The procedure first finds the first empty row on Sheet2 and stores its row number in the variable “lastRow“. The For Loop is assigned to check which option is selected in ListBox1 (employment status)  and TextBox3 (Gender) as well. Then, it initializes each cell of the last empty row to assign the data entered in TextBox1 (Employment Status) and TextBox3 (Gender)
  • The last Subroutine initializes to assign different Bullet buttons in ListBox1 and ListBox2 using the AddItem
  • Now see the output as given below.

Read More: Excel VBA: InputBox Type with Examples


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.

Vba code for multiple line in the input box

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.

InputBox with Multiple Lines Using vbMultiLine Argument

Read More: Excel VBA: Custom Input Box (7 Examples)


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.


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

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. He is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo