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 formname.show 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 UserForm1.Show 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 userform1.show command here. It is still in that module. That sub will be used to get our userform on screen.
Sub load_userform() UserForm1.Show End Sub
So users will run load_userform sub using some command, button, or macro assigned element. As the compiler runs the code userform1.show 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.