Excel VBA Tutorial 6 - Combo Box Excel VBA | How to Add Combobox in Excel VBA | exceldesk.in/2020/02/excel-vba-tutorial-6-combo-box-excel.html
Excel VBA Tutorial 6 - Combo Box Excel VBA Combo boxes are used for drop down list in Excel VBA user form or in Excel sheet. In some fields in the user form, we are given options. Like male and female for gender or drop down list of all country for Select country. For such a drop down list, we can drag the combo box from the tool box of VBA and set it in the user form.
1/6
How to Add Combobox in Excel VBA To add a combo box, click on the symbol of the combo box in the VBA toolbox. And click on the user form and drag the size.
Combo Box Properties -
2/6
1. Name - Any name can be given to Combo Box . 2. Combo Box Size - Option of drag and drop with the mouse is better to increase or Decrease the size. 3. Control Tip Text - When the application is run, the combo box has to type the text in the control tip text to show a text tip as soon as the mouse cursor is moved to the combo box. 4. Visible - To hide the text box, the Combo box will be hidden as soon as it is false in front of the Visible option. 5. Text - To show any text by default in the Combo box, you can type the words type in front of the text option. 6. With the option of Text Align, Back Color, Border Color, you can change the design of the Combo box.
Ways to add items to a combo box Method 1 Row Source You can add a list of name managers to combo box. Select the list of Excel sheet. Enter any name in the name manager's box. And press enter. 3/6
Type the name of the list of name managers by typing = in the row source of the properties of the combo box. E.g. Selecting the country list in the image below has named COUNTRY in the name manager. And typed =Country in the raw source of the combo box's properties. The list of Excel sheets will be added to Combo box.
This is one way to feel a combo box. There are other ways to feel combo box.
Method 2 - Simple Add Code
Code to add item to combo box
If you want to add any item in the combo box, double-click anywhere on the User form.
4/6
Such a code will open.
Private Sub UserForm_Click () End Sub
Select Initialize by clicking on the drop down of the procedure.
Private Sub UserForm_Initialize () End Sub
Selecting Initialize will run the code as soon as the form is opened. We have to add our list to Combo box as soon as the form is opened.
Now type the code under Private Sub UserForm_Initialize ().
me. Combo Box Name .AddItem "Item Name"
Your Code will be like this
Private Sub UserForm_Initialize () Me.Combobox1.AddItem "Male" 5/6
Me.Combobox1.AddItem "Female" End Sub (Add as many items as you want.)
Press F5 and run the project.
Method 3 (best way loop)
If you want to add unlimited list of excel sheet, then do it in the following Item Add Code. As you add items to the list, the items will be added to the combo box.
Loop Code Private Sub UserForm_Initialize() Dim i As Long Me.Combobox1.Clear For i = 2 To Application.WorksheetFunction.CountA(Sheet2.Range("A:A")) Me.Combobox1.AddItem Sheet2.Cells(i, 1).Value Next i End Sub Explained For i = 2 To Application.WorksheetFunction.CountA (Sheet number of list .Range ("A: A")) (Give the sheet number on your list instead of sheet2.)
Me.Combobox1.AddItem Sheet2.Cells (i, column number) .Value (You can copy and paste this code in your file.)
6/6