Mehedi Hasan
In general, the ComboBox is a special feature of Excel. When we need to use a list of options in Excel, we use this feature. Oftentimes, we may need to insert a ComboBox to leave a helpful note for other users so they can easily understand the process. However, combo boxes are very useful when it is necessary to show a large text in Excel. Moreover, they can be resized and moved around easily. In this article, I will show you the step-by-step procedures to insert ComboBox with Listindex in Excel VBA. Hence, read through and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
What Is ComboBox in Excel VBA?
ComboBox is a drop-down list based on the combination of a certain text box. From the drop-down list, we can choose our desired option. Also, we can link a cell with this list that will show the serial number of the chosen item. However, Excel ComboBox is available from Excel 2007 to 365. The UserForm ComboBox in Excel VBA is one of the UserForm controls in the ComboBox. In addition, a form has controls, including boxes or dropdown lists, that can help users who use the spreadsheet input or amend data more easily. In Excel, we may make a form by attaching text elements to a worksheet, such as buttons, combo boxes, list boxes, etc. Moreover, the data type UserForm is an Object. In addition, the Listindex indicates a list of all items which will appear as a drop-down menu. Thus they can be selected from the ComboBox drop-down menu.
Step-by-Step Procedure to Insert ComboBox with Listindex in Excel VBA UserForm
In Microsoft Excel, a UserForm is a custom-created dialog box that makes user information entry more manageable and user-friendly. However, the UserForm ComboBox in Excel VBA is one of the UserForm controls in the ComboBox. On the UserForm, you may choose and move a ComboBox. Hence, go through the following steps in order to insert a ComboBox with Listindex in VBA UserForm.
📌 Step 1: Select Dataset for VBA ComboBox with Listindex
For the purpose of demonstration, I have used the following sample dataset. However, it contains the Employee Name and Location, which I will generate from the VBA ComboBox.
📌 Step 2: Draw Label and ComboBox in Excel VBA
- Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
- Secondly, click the Insert button and select UserForm from the menu to create a user form.
- Thirdly, by doing this, you can see the UserForm is created, and a Toolbox will appear aside.
- Fourthly, there seem to be a variety of handy controls in the Toolbox. Now, I will employ a ComboBox.
- Then, a “+” icon will appear and drag the icon as per the requirement to create the ComboBox.
- After that, select Label from the Toolbox to draw a Label on the UserForm.
- Now, change the Label name accordingly.
- From the ComboBox Properties, change the name of the ComboBox.
- Again, go to the Toolbox and select CommandButton.
- Next, change the name of the button to Submit.
- Afterward, press the F5 key to run the UserForm and it will look like the image below.
📌 Step 3: Insert VBA Code to ComboBox
- In the beginning, go to the UserForm and double-click the Submit button.
- Then, it will show an auto sub like the following image.
- Again, go to UserForm and clicl on UserForm.
- Similarly, select Initialize from the right drop-down box.
- After that, write the following code into the Visual Basic Editor to add the desired items.
Private Sub UserForm_Initialize()
'Insert Items in the ComboBox
LocationComboBox.AddItem "California"
LocationComboBox.AddItem "Texas"
LocationComboBox.AddItem "Arizona"
LocationComboBox.AddItem "Washington"
LocationComboBox.AddItem "Michigan"
End Sub
- Next, press the F5 key to run the code, and the Location ComboBox will appear will all the added items.
- Finally, insert the VBA code to select the Range of the input cell and run it. Here, I will input the location in C5.
Private Sub CommandButton1_Click()
Range("C5") = Me.LocationComboBox
Unload Me
End Sub
Read More: How to Use VBA to Populate ComboBox List from Range in Excel
📌 Step 4: Input Data with ComboBox
- At first, select a Location and hit the Submit button.
- In the end, the data will automatically be inserted into your preferred Range.
Read More: How to Create a Searchable ComboBox with VBA in Excel
Final Output
Last but not least, change the Range of the VBA code in order to change the data input location according to your choice. In addition, repeat Step 4 to add more data. Finally, the output will appear as below.
Conclusion
These are all the steps you can follow to create ComboBox with Listindex in Excel VBA. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit ExcelDemy.com.
