August 6, 2020

How to Use ComboBox in VBA Excel Userform?

A combobox on a userform is like a drop down menu, which can be used to pick an option from the available options. It is like the drop down we create using data validation on worksheets.
In most digital forms, you must have seen at least one combo box. It could have been for selecting a state from a list of states, a course from a list of courses, a country from a list of countries, etc. In this article, we will learn how to add a combobox in a VBA userform, how to initialize the combobox with values and how to retrieve value from combobox.

Adding A ComboBox to User Form

To add a combobox to a userform, you first need to have a userform. Dah!.

So open VBE using CTRL+F11. Right click on the workbook, go to insert and click on Userform. You have a userform added. You can read further about userforms in VBA here.

Now from the toolbox, choose combobox. It’s the fourth option on the top row. If you can’t see the toolbox. Go to View in the menu, click on Toolbox. The toolbox will appear.

I have added a label of states because this combobox will contain a list of some states and a submit button to submit the form.

How to Initialize a combobox in userform?

This is the part where most people make mistakes. Sometimes learners write the code for loading values in combobox then submit buttons. And when they load the userform, the combobox doesn’t show any values.

To load the combobox, you should write the code, before loading the userform. This can be done in the module, c or the button where you load the form. The code to load the combobox list should come before the command.

Loading Combobox in UserForm Calling Subroutine

Let’s say I want to hard code some array in the state combobox. So I will write a subroutine which will load the userform to fill information. See the below code.

Sub load_userform()

 states = Array("Delhi", "UP", "UK", "Gujrat", "Kashmir")

 UserForm1.ComboBox1.List = states


End Sub

I define an array “states” that contains a few names of Indian states. Now I use the userform name to access combobox as combobox is a part of userform.

My userform’s name is userform1. And combobox’s name is combobox1. These are default names.  You can change them from the property window.

Next I initialize the combobox’s list property with states.

And then I show the userform1 using the show command.

When you run the above code, the combobox will have all the states listed.

Initializing Combobox Using UserForm_Initialize() Event.

Another method of loading combobox components on userform is using userform event Initialize. This event runs before the userform loads. Anything you want to do before the userform loads, you can do it here. One of those things is loading combobox elements.

So double click on userform to open the coding area in the userform object. Now from the left dropdown menu select userform. Then from the right dropdown menu select initialize.

An empty sub name UserForm_Initialize() will be inserted. Anything written in this sub will be executed before the userform shows up.

So we write the initialization code for combobox here.

Private Sub UserForm_Initialize()

 states = Array("Delhi", "UP", "UK", "Gujrat", "Kashmir")

 UserForm1.ComboBox1.List = states

End Sub

Note that we don’t have command here. It is still in that module. That sub will be used to get our userform on screen.

Sub load_userform()


End Sub

So users will run load_userform sub using some command, button, or macro assigned element. As the compiler runs the code command, it immediately runs the useforma_initialize()  event. Afterwards it shows the userform to users.


If you write the code to initialize combobox with values in command button1 click, combobox will not show anything.

To initialize the combobox with values, load it in any event that occurs before the user reaches the combobox to fill.

Retrieving value From ComboBox

So we use forms to get some information. And to use that information, we need to retrieve them. To get any value from the userform combobox we use combobox.value property. Generally we retrieve the values from form elements once the user completes the form and submits the form using the submit button. So we can use the commandbutton_click event. So insert a command button and write submit on it, if you already don’t have it. Now double click on it and you will be inside the commandButton_click event.

Now create a variable to store the value. Save it on a sheet if you like. Finally exit the form using Unload Me command. As shown in the below code.

Private Sub CommandButton1_Click()

 State = ComboBox1.Value

 ThisWorkbook.Worksheets("sheet1").Range("A1") = State

 Unload Me

End Sub

So yeah guys, this is how you use combobox in VBA userforms. I hope I was explanatory and to the point. If you have any questions regarding this topic or any other VBA related questions, ask me in the comment section below.

Leave a Reply

Your email address will not be published. Required fields are marked *