Excel VBA ComboBox

Combo Box can be compared to a dropdown list in Excel in worksheets. We used data validation to provide a dropdown. But in VBA, a UserForm feature provides a dropdown in any UserForm. But, if we want to use a combo box in Excel, we can access it from the “Developer” section. From there, we can create combo boxes for individual or multiple cells.

Combo Box is very similar to the dropdown list we have in an excel worksheet. With the combo box, we can store predetermined values so that users make the selection from the list available from the combo box. The combo box is generally used with UserForms to get the users’ input.

UserForms are useful but having other tools on the user form is what makes the user form so special. One of the tools that we often use as a tool for UserForm is “Combo Box.”

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA ComboBox (wallstreetmojo.com)

Top 2 Ways of Creating a VBA ComboBox

#1 – Using Direct Coding

First, we will see how to use Combo Box with the worksheetUse Combo Box With The WorksheetCombo Box in Excel is a type of data validation tool that can create a dropdown list for the user to select from the pre-determined list. It is a form control which is available in the insert tab of the developer’s tab.read more. Next, open any worksheets in the Excel workbook, and go to the Developer tabDeveloper TabEnabling the developer tab in excel can help the user perform various functions for VBA, Macros and Add-ins like importing and exporting XML, designing forms, etc. This tab is disabled by default on excel; thus, the user needs to enable it first from the options menu.read more. Under this tab, we have a tool called “Insert.” Click on this, and under this, we have two options Active X Controls and Form Controls in excelForm Controls In ExcelExcel Form Controls are objects which can be inserted at any place in the worksheet to work with data and handle the data as specified. These controls are compatible with excel and can create a drop-down list in excel, list boxes, spinners, checkboxes, scroll bars.read more.

From “Active X Controls,” choose “Combo Box.”

Now you can draw this object on any of the worksheets.

Right-click on the combo box and choose the “Properties” option.

When you choose properties, it will open up a huge list of combo box properties.

We will give a list of department names for this combo box, so change the name property of the combo box to “DeptComboBox.”

This combo box will be referred to by the name “DeptComboBox.” We need to give pre-determined department names, so here I have a list of department names.

Double click on the combo box, which will take you to the VBA macroVBA MacroVBA Macros are the lines of code that instruct the excel to do specific tasks, i.e., once the code is written in Visual Basic Editor (VBE), the user can quickly execute the same task at any time in the workbook. It thus eliminates the repetitive, monotonous tasks and automates the process.read more procedure.

But we need to see these department names when the workbook opens up, so double click on “ThisWorkbook.”

From the dropdown list, choose “Workbook.”

From the options, choose “Open.”

Now it will create a blank like the one below.

Inside this macro, enter the below code.

Code:

Private Sub Workbook_Open()

With Worksheets(“Sheet1”).DeptComboBox .AddItem “Finance” .AddItem “Marketing” .AddItem “Merchandising” .AddItem “Operations” .AddItem “Audit” .AddItem “Client Servicing” End With

End Sub

Now save and close the workbook. When you reopen the workbook, we can see its department names.

#2 – Using UserForm

Another way to add values to the combo box is using the UserForm. First, give a name to cells as “Department.”

Next, enter Visual Basic EditorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more and insert “UserForm” from the “INSERT” option.

Now, it has created the new UserForm.

Next to the UserForm, we can see “Toolbox” from this toolbox. Then, finally, we can insert “ComboBox.”

Now, the combo box is embedded in the UserForm. To open the properties option, select the combo box and press the F4 key to open the properties window.

Scroll down the “Properties” tab and choose “RowSource.”

For this “RowSource,” enter the name we had given to department name cells.

Now, this combo box holds the reference of the name “Department.”

Now, run the UserForm by using the “Run” button.

Now, we can see a list of department names in the combo box on the UserForm.

Practically UserForm is associated with a combo box, text box, and many other tools. Therefore, we will create a simple data entry UserForm with a text box and combo box.

Create a UserForm like the one below.

Create two “Command” buttons.

Double click on the “SUBMIT” button. It will open up below the macro.

Inside this macro, add the below code.

Private Sub CommandButton1_Click()

Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(LR, 1).Value = TextBox1.Value Cells(LR, 2).Value = ComboBox1.Value

End Sub

Now, double-click the “CANCEL” button and add the code below.

Now, in the worksheet, create a template like the one below.

Now, run the UserForm. It will open up like this.

Enter the employee name and pick the department name from the combo list.

Click on the “SUBMIT” button and see the magic.

We got the values entered in the table format we have created.

Things to Remember

  • The COMBO BOX has its properties as well.Adding values to the list comes in two ways. One is a coding way, and another is a range name reference.The COMBO BOX is usually part of the UserForm.

This article has been a guide to VBA Combo Box. Here, we discuss how to insert and use a combo box using VBA coding in Excel along with the downloadable template. Below you can find some useful Excel VBA articles: –

  • List Box in ExcelName Box in ExcelVBA InputBoxExamples of Checkbox in Excel