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.

Fig 1 – Open Visual Basic

Once we click on the Visual Basic option, excel will open Visual Basic Editor in a new window.

Fig 2 – Visual Basic Editor

In the Visual Basic Editor, we need to insert user form for this we just need to click on the Insert UserForm option.

Fig 3 – Insert UserForm

Once we click on Insert UserForm, excel will automatically insert a user form.

Fig 4 – 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.

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.

Fig 5 – Adding 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.

Fig 6 – Changing Caption & TextAlign

We will also change the background color for this Select Label then Properties Window then BackColor here we choose Palette from Choose Color.

Fig 7 – Changing BackColor

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.

Fig 8 – Copying Labels For All Fields

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.

Fig 9 – Changing Labels

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.

Fig 10 – Align All Labels to Left

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.

Fig 11 – Adding TextBox

Similarly, we will add TextBox for Age Field. Also, we will add SpinButton along with TextBox for age field.

Fig 12 – Adding SpinButton

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.

Fig 13 – Adding ComboBox

Finally, for our Technical Content Writer field we will be using CheckBox. For this go to Toolbox and then drag the CheckBox to UserForm.

Fig 14 – Adding CheckBox

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

Fig 15 – Changing Caption

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.

Fig 16 –  Select Everything & Copy It

Once we have copied everything to clipboard, we need to delete all of them and add frame to our user form.

Fig 17 – Adding Frame

After we have added the frame, we need to paste all the clipboard items that we have copied and will get the following output.

Fig 18 – 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 “GeeksForGeeks“.

Fig 19 – Adding Caption To Frame & User Form

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. 

Fig 20 – Adding Command Button

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.

Fig 20 – 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.

Fig 21 – To Run User Form

Once we run our user form, we will get the following output.

Fig 22 – Running User Form

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.

Fig 23 – Age Field Name

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.

Fig 24 – Link Spin Button with Age Field

Once we have added the code in VBA Editor. We will run our user form and will use spin button to change the value.

Fig 25 – Age Field

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.

Fig 26 – Branch List

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.

Fig 27 – Range Name

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.

Fig 28 – Adding Range Name To RowSource

After this when we run our VBA Code, we will get the list of branch visible in the drop-down menu.

Fig 29 – Branch List

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.

Fig 30 – Adding Columns To Excel

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

Fig 31 – Submit Button Function

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.

Fig 32 – User Form Data Storing

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

Fig 33 – Cancel Button Function

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

Fig 34 – Reset Button Function

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.

Fig 35 – Adding ActiveX Control: Command Button

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.

Fig 36 – Adding Function to Command Button

After this, we need to disable the Design Mode and Click Command Button (ActiveX Control). It will open our user form.

Step 12: Output