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.
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.
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.
Notes:
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.
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.
Related Articles:
Getting Started With Excel VBA UserForms| I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topics using one example and step by step guide.
VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.
Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.
ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
