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