Insert ComboBox with Listindex in Excel VBA (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Excel VBA ComboBox Listindex


How to Insert ComboBox with Listindex in Excel VBA UserForm: Step-by-Step Procedure

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.

VBA ComboBox Listindex Sample Dataset


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

Draw Label and ComboBox in Excel VBA

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

VBA ComboBox Listindex in Excel

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

Insert VBA Code to ComboBox

  • 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

VBA ComboBox Listindex

  • 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


📌 Step 4: Input Data with ComboBox

  • At first, select a Location and hit the Submit button.

VBA ComboBox Listindex

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

VBA ComboBox Listindex Final Output


Download Practice Workbook

You can download the workbook used for the demonstration from the download link 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan
Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo