How to Create an Input Box With Multiple Inputs in Excel Using VBA?
The Input Box is a dialogue box that helps users to take a value and do later computations according to the entered value. The input box is similar to the message box, but the message box is used to display the data while the input box is used to enter the data. By default, a message box displays only the Ok button in its conversation box, whereas an input box displays both the Ok and Cancel buttons. You can specify the type of data to be returned.
In excel VBA Macro, Input Box is one of the most commonly used functions. In this example, we will learn how to create an input box using excel VBA which consumes multiple data from the end-user. For this, we will be creating a user form.
Step By Step Implementation
Step 1: Insert Visual Basic Editor
To insert Visual Basic Editor in excel please refer to How to Insert and Run VBA Code in Excel?
Step 2: Insert User Form
In this step, we need to add a user form to our VBA. For this, we need to go to the Developer tab on the top of the ribbon and then select Visual Basic.
Once we click on the Visual Basic option, excel will open Visual Basic Editor in a new window.
In the Visual Basic Editor, we need to insert user form for this we just need to click on the Insert UserForm option.
Once we click on Insert UserForm, excel will automatically insert a user form.
If the Toolbox window doesnβt get open automatically, then Click on View and then Toolbox. It will open the toolbox window.
Step 3: Adding Labels to User Form
In this step, we will add the required labels we need in our user form. For this example, we will be using the following labels in our user form.
Labels | Name | Caption |
Frame | w3wiki | w3wiki |
TextBox | Name | Name |
TextBox | Age | Age |
ComboBox | Branch | |
CheckBox | Technical Content Writer |
First, we need to add Label to our User Form. In order to add a label, we go to Toolbox and then drag the Label to User Form.
Once we add Label to our User Form, we need to format it using the Properties Window.
Note: If by default Properties Window is not visible, we can add it. For this Select Label then View and then Properties Window.
Now, we will format our label, for this, we need to Select Label then Properties Window, and then Caption & Text Align.
We will also change the background color for this Select Label then Properties Window then BackColor here we choose Palette from Choose Color.
Now, we need to copy this Label for all of our User Form fields. For this Select Label and then copy with Ctrl+C and then paste it with Ctrl+V, all the labels below one another.
Once, we have copied and pasted the required labels we need to change the fieldβs name according to our requirements. For changing fields name we will use the Properties Window as we have done above. Once we update all the labels as per our requirement, we will get the following output.
Before moving further, we can align our labels. For this Select Labels and then choose Format on the top of the ribbon then select Align and choose Left. This will align all the Labels to left in a straight line.
Note: To select all labels, we need to drag our cursor over all the labels.
Step 4: Adding Input Box
In this step, we will add the required fields to take input from user. Here, we will use TextBox for Name and Age. For Branch we will be using ComboBox and for the Technical Content Writer field we will be using CheckBox.
First, we will add TextBox for Name Field. For this go to Toolbox and then drag the TextBox to UserForm.
Similarly, we will add TextBox for Age Field. Also, we will add SpinButton along with TextBox for age field.
Now, For Branch Field we will add ComboBox. In order to add this we need to go to Toolbox and then drag the ComboBox to User Form beside branch field. ComboBox is used for adding list elements.
Finally, for our Technical Content Writer field we will be using CheckBox. For this go to Toolbox and then drag the CheckBox to UserForm.
We need to change the caption for our CheckBox. For this Select CheckBox and then in Properties Window click Caption. Here, we are changing it to βYesβ.
Step 5: Adding Frame
Now, we need to add our user form in a frame. For this, we need to go to Toolbox and then drag the Frame to User Form. But if we drag the frame over our user form, it gets overlapped on our fields and attributes. So, First We need to select all the fields and attributes by dragging over the user form and then copy it. After that we will delete everything and add frame to our user form and then paste the copied fields and attribute.
Note: Make sure you have copied everything from user form, before deleting it.
Once we have copied everything to clipboard, we need to delete all of them and add frame to our user form.
After we have added the frame, we need to paste all the clipboard items that we have copied and will get the following output.
Now, we have added frame to our user form. We can also format the Frame as per our requirement using the Properties Window. Here, we are changing the Captions for frame as βFill the Formβ and for UserForm as βw3wikiβ.
Step 6: Adding Button
In this step, we will add buttons to our user form. For adding button, we go to Toolbox and then drag the CommandButton to User Form.
Now, we will change the text of button using Caption from Properties Window. We will change it as per requirement we will get following output.
Before moving ahead, we can run our VBA code. To run VBA code please refer How to Insert and Run VBA Code in Excel? For this Select User Form and then Run Button in Menu.
Once we run our user form, we will get the following output.
In above user form, we can add text to our Name Field. But for Age and Branch we need to define their functionalities.
Step 7: Adding Age Functionality
In this step, we will add functions to our Age Fields. When we click on our age field textbox we will get the name of textbox that we need to link to SpinButton. So, that age gets updated whenever we click on spin button.
As above, we can see age field name is TextBox2. Similarly, we will check spin button name. After that we need to link the spin button with age field name. For this Double-click on Spin Button. It will open VBA code editor. Where we need to write the following code.
TextBox2.Value = SpinButton1.Value
We will also set min and max value for age field. Here, we are using min value as 18 and max as 62.
Once we have added the code in VBA Editor. We will run our user form and will use spin button to change the value.
Step 8: Adding Branch Functionality
In this step, we will add function to our Branch Field. For this, we need list of branches (CSE, Mech, IT, Electrical, Chemical). We will create a new sheet in excel and add list of the branches to it.
Now, we need to give a name to the Range of list. For this Select All Row and then Add Name in Name Box. Here, we are using range name as branch_list.
After this, we need to link the range name of our branch list to our branch field combobox. For this Select ComboBox (Branch Field) then in Properties Window select Row Source and add the range name there.
After this when we run our VBA Code, we will get the list of branch visible in the drop-down menu.
Before moving further, we need to add columns for our fields in the excel sheet. Which will store data entered by users in our user form.
Step 8: Adding Submit Button Functionalities
In this step, we will add function to our Submit Button. For this we need to do Double-Click on Submit Button then in VBA Code Editor click Add Code. We will be adding the following code in VBA code editor. Make sure that you have changed the cell & fields name according to your excel sheet.
Sheet1.Activate
Range(βA1β).End(xlDown).Offset(1, 0).Value = TextBox1.Value
Range(βB1β).End(xlDown).Offset(1, 0).Value = TextBox2.Value
Range(βC1β).End(xlDown).Offset(1, 0).Value = ComboBox1.Value
If CheckBox1.Value = True Then
Range(βD1β).End(xlDown).Offset(1, 0).Value = βYesβ
Else
Range(βD1β).End(xlDown).Offset(1, 0).Value = βNoβ
End If
Unload Me
After we add function to our submit button. We will run our user form and enter the data. The data gets stored into the excel sheet.
Step 9: Adding Cancel Button Functionalities
In order to add cancel function. We will need to write the following code. It will remove our user form from the foreground.
Unload Me
Step 10: Adding Reset Button Functionalities
In this step, we will add function to our reset button. We will be using following code. It will make all fields to blank(empty).
TextBox1.Value = "" TextBox2.Value = "" ComboBox1.Value = "" CheckBox1.Value = False
Step 11: Adding Macros for User Form
In this step, we will add a macro to access our user form. Every time the end user need not to go to developer option and access our user form. We will add a control command button on clicking user will access user form. For this go to Excel Sheet and then in Developer tab on the top of the ribbon and then in Enable Design Mode group select Insert and then drag the Command Button (ActiveX Control) to Excel Sheet.
After we inset the Command Button, we need to add function to it. So that, whenever any user clicks on the button, it will open our user form. To add functions, it we need to Double-Click Command Button and Add Functions. Here, we are also formatting our command button using Properties Window.
After this, we need to disable the Design Mode and Click Command Button (ActiveX Control). It will open our user form.
Step 12: Output
Contact Us