Excel VBA: Input Box with Options

Looking for a way to create an input box with options using VBA in Excel? Then, this is the right place for you.

We can use the input box using which we can enter values that can be used values to various operations. Which can make our code more dynamic. In this article, we have shown how you can create an input box with options.

Below we have presented an input box using which we are now adding items inside the worksheet.

Basic Syntax of Input Box in Excel VBA

The basic Syntax of an inputbox is given below:

InputBox(Prompt, Title, Default, XPos, YPos, HelpFile, HelpContextID)

Is It Possible to Create an Input Box with Options in Excel VBA?

The traditional Excel inputbox does not allow users to choose between options. You only can enter values, or accept a range of cells from the user. Multiple option choice is completely invalid in Inputbox in Excel.

If you want to provide an input box with multiple choices in Excel, you can use VBA programming to create a user form that includes multiple checkboxes or a list box. The user can then select one or more options from the list and click a button to submit the choices.

Alternatively, you can use Excel’s built-in data validation feature to create a drop-down list with multiple choices. This allows users to select one option from a list of choices, but does not allow for multiple selections at once.

Userform: Best Way to Create an Input Box with Options in Excel

What is the user form? How does that work? Show your created user form’s image.

User can customize their own input boxes with options. This might take some time, but the outcome is very convenient to use. You can see the following example of an inputbox that is created using the VBA Userform. In the VBA userform, you can modify the input data types, ranges of data, etc.

Definition:  A user form is part of a software program’s graphical user interface (GUI). It lets users interact with the program by entering data or making choices. It usually has input fields, checkboxes, dropdown menus, buttons, and other controls that let users tell the application what to do. User forms are often used in software to do things like enter data, set up settings, and make changes. They can help improve the user experience by making the interface for interacting with the app easy to understand and use.

The UserForm control or components can be added using the VBA code or manually by using the toolbox. Then those inputs can be manipulated according to the need of the user. The whole process is going to be given below in details.

How to Launch VBA Editor in Excel

You can easily launch the VBA editor by pressing Alt+F11 or using the Visual Basic command from the Developer tab. As shown in the video below,

• After entering the Developer tab, tango to Insert > Module, you will see an editor where you can put necessary code.

Excel VBA: Input Box with Options (Create with These Steps)

Below we will show how you can create a custom inputbox in Excel using VBA macro. User can easily edit or modify this code or userform to their needs. In order to avoid any kinds of error or compatibility issues, try to use the Excel 365 edition

Step 1: Initiation of Userform to Create Input Box with Options

• First, we need to initialize and insert the UserForm inside the VBA editor.
• For this, first, go to the Developer tab in the worksheet and then click on Visual Basic.
• Then click on Insert and from the dropdown click on the UserForm.
• This will initiate the UserForm in the editor and you can now resize and add controls in the UserForm.

Read More: How to Use Excel VBA InputBox with Example

Step 2: Insert TextBoxes and Labels to Add Options in VBA Input Box

• Now we are going to add the TextBox and the Label inside the VBA UserForm.
• For this, Select the Textbox tool from the toolbox and then draw the textbox inside the userform editor.
• After this, select the Label tool from the toolbox and then again draw the label on the userform.
• Then from the properties window, edit the caption of the label and set it to Insert Id.
• Then repeat the same process and enter 2 more text boxes and their label together.
• After this, set the label to Name and Date of Birth. As shown below:

Read More: Excel VBA: Create InputBox with Multiple Inputs

Step 3: Insert Combo Box and Label for Dropdown List in Excel

The combobox which resembles the dropdown menu that we use regularly are going to be inserted into the userform.Later it is going to be labeled.

•  We can enter the Combo Box from the toolbox and add the label from there too.
• Change the Label caption to Department.

Read More: Excel VBA Input Box with Drop Down List

Step 4: Insert Option Buttons to Create Input Box with Options

In this step we are going to insert optionbutton inside a frame. Later they are going to be labeled properly

• First, we need to enter A frame inside the UserForm using the toolbox.
• Then we can enter the OptionButton from the toolbox inside the added frame and add the label from there too.
• Change the Label caption to Department.
• Repeat the same process and add another OptionButton from the toolbox and Label.
• Change the first OptionButton Label to Male and the second OptionButton label to Female.

Step 5: Insert Checkboxes

• First, we need to enter A frame inside the UserForm using the toolbox.
• Then we can insert a checkbox inside the frame and add a label also.
• Change the Label caption to Data Analysis.
• Repeat the same process and add 4 more CheckBoxes from the toolbox and Label them.
• Change the Label caption to Machine Learning, Artificial Intelligence, Big Data, Power BI.

Step 6: Insert Necessary Buttons

• In the final steps, we need to add some additional command buttons at the bottom of the UserForm.
• For this select the Command Button from the toolbox.
• Repeat the same process 2 more times.
• And then change the caption to Submit, Reset and Cancel.

Step 7: Change Alignments of Components

After all the necessary controls are done adding, we can realign and resizes the Controls

• The final form of the userfrom is given below.

Step 8: Change Font Sizes and Styles

We have now the text labels and caption over the controls, we can now change the font styles and sizes using the font changes

• For this, we showed the change of the text boxes label caption font sizes and types.
• The final outcome will look like the below image.

Step 8: Set the VBA Code for Initializing the Userform

For Textboxes

We can enter the following code which will execute when the userform initializes.

Private Sub UserForm_Initialize()
ComboBox2.AddItem "Computer Science"
ComboBox2.AddItem "Software Engineering"
ComboBox2.AddItem "Statistics"
End Sub

• This part of the code will run when the UserForm initializes.
• This code will add the names of the items as “Computer Science”, “Software Engineering”, and “Statistics” into the combobox.
• If we run the code experimentally now, we can see that the items are now added in the combobox and we can change and choose the items from a dropdown list as shown in the image below.

Step 9: Write Code for Cancel Button, and Reset Button

Code for Unloading the UserForm

Private Sub CommandButton2_Click()
Unload Me
End Sub

This part of the code actually denotes the Cancel button in the UserForm.

• When this commandbutton is pressed, this part of the code will execute and close the userform.

Private Sub CommandButton3_Click()
    Dim myCtrl As Control
    For Each myCtrl In Me.Controls
    If TypeOf myCtrl Is MSForms.TextBox Or TypeOf myCtrl Is MSForms.ComboBox _
    Or TypeOf myCtrl Is MSForms.OptionButton Or TypeOf myCtrl Is MSForms.CheckBox Then
            myCtrl.Value = ""
        End If
    Next myCtrl
End Sub

Private Sub CommandButton3_Click()

 Dim myctrl As Control
    For Each myctrl In Me.Controls

 If TypeOf myctrl Is MSForms.TextBox Or TypeOf myctrl Is MSForms.ComboBox Or TypeOf myctrl Is MSForms.OptionButton Or TypeOf ctrl Is MSForms.CheckBox Then

myctrl.Value = ""

End If
    Next myctrl
End Sub

• This part of the code is for the Reset button. When this button is clicked, all of the UserForm controls are going to be reset and set back to the initial condition.

Read More: How to Use VBA Input Box with Buttons in Excel

Step 10: Write code for Male OptionButton and Female OptionButton to Create Input Box with Options

For Optionbutton1:

Private Sub OptionButton1_Change()
If OptionButton1 = True Then
gender = "Male"
ElseIf OptionButton2 = True Then
gender = "Female"
End If
End Sub

Private Sub OptionButton1_Change()

If OptionButton1 = True Then
gender = "Male"

ElseIf OptionButton2 = True Then
gender = "Female"

End If
End Sub

For Optionbutton2

Private Sub OptionButton2_Change()
If OptionButton2 = True Then
gender = "Female"
ElseIf OptionButton1 = True Then
gender = "Male"
End If
End Sub

OptionButton2_Change()

If OptionButton2 = True Then
gender = "Female"

ElseIf OptionButton1 = True Then
gender = "Male"

End If
End Sub

• This part of the code denotes any changes in the optionbutton, which means if there is any change in the status of the optionbutton, then this code will execute.

Step 11: Write VBA Code for Submit Button

Code for CheckBoxes

Dim career_choice() As String
If CheckBox1.Value = True Then
ReDim Preserve career_choice(i)
career_choice(i) = "Data Analysis"
i = i + 1
End If
If CheckBox2.Value = True Then
ReDim Preserve career_choice(i)
career_choice(i) = "Machine Learning"
i = i + 1
End If
If CheckBox3.Value = True Then
ReDim Preserve career_choice(i)
career_choice(i) = "Artificial Intelligence"
i = i + 1
End If
If CheckBox4.Value = True Then
ReDim Preserve career_choice(i)
career_choice(i) = "Big Data"
i = i + 1
End If
If CheckBox5.Value = True Then
ReDim Preserve career_choice(i)
career_choice(i) = "Power BI"
i = i + 1
End If

Check Whether User Keep Any Input Empty and Correct Data Format

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox4.Value = "" _
Or ComboBox2.Value = "" Or Len(gender) = 0 Or i = 0 Then
MsgBox "Enter all the input values properly"
GoTo ErrorHandler
ElseIf Not IsDate(TextBox4.Value) Then
MsgBox "Please enter a valid date in the format mm/dd/yyyy."
GoTo ErrorHandler
Else

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox4.Value = "" _
Or ComboBox2.Value = "" Or Len(gender) = 0 Or i = 0 Then
MsgBox "Enter all the input values properly"
GoTo ErrorHandler

ElseIf Not IsDate(TextBox4.Value) Then
MsgBox "Please enter a valid date in the format mm/dd/yyyy."
GoTo ErrorHandler

Else

Place Values in the Worksheet If Inputs Are Alright

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 3).End(xlUp).Row
ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox4.Value
ActiveSheet.Cells(lastRow + 1, 5).Value = ComboBox2.Value
ActiveSheet.Cells(lastRow + 1, 6).Value = gender
On Error Resume Next
For i = 0 To UBound(career_choice)
If IsEmpty(ActiveSheet.Cells(lastRow + 1, 7).Value) Then
ActiveSheet.Cells(lastRow + 1, 7).Value = career_choice(i)
Else
ActiveSheet.Cells(lastRow + 1, 7).Value = ActiveSheet.Cells(lastRow + 1, 7) _
.Value & ", " & career_choice(i)
End If

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 3).End(xlUp).Row

ActiveSheet.Cells(lastRow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastRow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastRow + 1, 4).Value = TextBox4.Value
ActiveSheet.Cells(lastRow + 1, 5).Value = ComboBox2.Value
ActiveSheet.Cells(lastRow + 1, 6).Value = gender

On Error Resume Next

For i = 0 To UBound(career_choice)

If IsEmpty(ActiveSheet.Cells(lastRow + 1, 7).Value) Then
ActiveSheet.Cells(lastRow + 1, 7).Value = career_choice(i)
Else
ActiveSheet.Cells(lastRow + 1, 7).Value = ActiveSheet.Cells(lastRow + 1, 7) _
.Value & ", " & career_choice(i)
End If

Code for Autofitting the Columns and Set the Inputs Back to Initial State

ActiveSheet.Columns.AutoFit
Dim myControl As Control
For Each myControl In Me.Controls
    If TypeOf myControl Is MSForms.TextBox Or TypeOf myControl Is MSForms.ComboBox _
    Or TypeOf myControl Is MSForms.OptionButton Or TypeOf myControl Is MSForms.CheckBox Then
        myControl.Value = ""
    End If
Next myControl
ErrorHandler:
End Sub

ActiveSheet.Columns.AutoFit

Dim myctrl As Control

    For Each myctrl In Me.Controls

       If TypeOf myctrl Is MSForms.TextBox Or TypeOf myctrl Is MSForms.ComboBox _
        Or TypeOf myctrl Is MSForms.OptionButton Or TypeOf myctrl Is MSForms.CheckBox Then

            myctrl.Value = ""

     End If
    Next myctrl
ErrorHandler:

Read More: How to Handle VBA InputBox Cancel Button in Excel

Step 12: Insert Button in Worksheet to Launch UserForm

We can add a command button and assign a code to this button such that when the button is pressed, the userform will show up.

• We can add the command button from the Developer tab and from there click on the Command Button from the Insert option.
• Then right-click on the Command Button and from the context menu click on the Properties from the Properties window and alter the caption to Input Student Data.
• Then again right click on the command button and then click on the View Code button. And then in the code editor window enter the following code,

Private Sub CommandButton1_Click()
Student_Data_Form.Show
End Sub

• Click on the Save icon.

After we can see that if we click on the Input Student Data, the userform will show up.  

How to Use Excel Input Box to Fill Cell

We can add random text or our desired inputs in the worksheet’s certain cell using the inputbox. For this, we can use the following code,

Sub InputBoxFillCell()
Dim userInput As Variant
userInput = InputBox("Enter your input:", "Input Box")
ActiveCell.Value = userInput
End Sub

• Here we can select the cell where we can enter the input value. After then we can run the code to pop up the input box.
• In the input box we can enter the input value and press OK.
• After pressing OK we can see that the selected cell now has the input value.

Frequently Asked Question

• Application InputBox vs InputBox

The main difference between an application. inputbox and inputbox method are that the Application. inputbox gives users versatile input parameters. Users can specify the data types which are going to be entered,

For Example, we can look at the below code, where we specified various parameters inside the inputbox method.

Sub ExampleInputBox()
Dim myValue As Variant
myValue = Application.InputBox("Enter your name:", _
"Name Input", "John Doe", Type:=2, _
HelpFile:="C:\HelpFile.hlp", _
HelpContextID:=1000, Left:=500, Top:=500)
MsgBox "You entered " & myValue
End Sub

Here, in the code, we have the inputbox title and default value already placed inside the inputbox.

The inputbox will look something like the one below, where we can see the default name already showing with the boxes name and title name. When the user clicks the “?” button or presses F1 while the input box is active, the input box passes the HelpFile and HelpContextID parameters to the help system, which uses them to display the appropriate help topic.

• The Buttons in InputBOx in Excel VBA

With the InputBox function in Excel VBA, you can show a box that asks the user to type something in. This function can show up to three buttons, OK, Cancel, and Help. Here’s what each button does:

• OK button – You can use this button to accept the input that the user has entered in the input box. When the user clicks the OK button, the InputBox function returns the value that the user entered.
• Cancel button – Use this button to cancel the input operation. When the user clicks the Cancel button, the InputBox function returns the value False.
• Help button – You can use this button to display context-sensitive help for the input box. When the user clicks the Help button, the InputBox function can display help information that is associated with the input box.

Here we can see a simple example of an inputbox with the buttons mentioned above. The inputbox also has a fixed default value set up.

Things to Remember

There are some things that you should take into consideration.

• Input values need to be in the proper type. And you can set it fixed in the inputbox input values. If you enter values other than the set type, then there will be some warning.
• Always remember to validate your output results. Be sure to check the output result by matching several input and output values.
• Use appropriate formatting for the inputbox, including font size, color, and alignment. This will help to make the inputbox more visually appealing and easier to use.

Download Practice Workbook

Download the following workbook to practice by yourself.

Conclusion

In this whole article we discussed how you can create and open input boxes using the VBA userform step by step with various types of examples .

Related Articles

• How to Create Yes-No InputBox with Excel VBA
• Excel VBA InputBox with Multiple Lines
• Excel VBA: InputBox with Password Mask