Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Combo Box in Excel and VBA

Combo Box in Excel

Table of Contents

  • What is Combo Box in Excel?
  • Types of Combo Boxes in Excel
  • How to Create Form Controls Combo Box?
  • How to Create an Active X Controls Combo Box?
  • Redesign Combo Box in VBA

What is Combo Box in Excel?

Combo 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. Combo Boxes in Excel are a great tool, which does not require any VBA coding knowledge.

By creating a drop list through Combo Box, it allows more accountability over user inputs. The user cannot go for manual entry of their own choice. I assume if you are learning about Combo Box, you are already aware of Drop Down Data Validation techniques. For example, a general validation looks like this.

Combo Box

In this article, we will learn about Combo Box in excel and we will also learn about Excel VBA Combo Box as well.

Now, first let’s look into Combo Box in Excel.

The first things you need to do is make sure Developer tab is enabled in your Excel ribbon. It should be visible like the below one.

combo in excel step 0

If not please enable the Developer tab by following the below steps.

  • Go to FILE

Combo in Excel - step 1

  • Click on options.

Combo in Excel - step 2

  • Go to Custom Ribbon and make sure developer tab checkbox is ticked.

Combo in Excel - step 3

Types of Combo Boxes in Excel

Combo in Excel - step 4

  1. Form Controls Combo Box
  2. Active X Controls

How to Create Form Controls Combo Box in Excel?

Now, we have enabled our developer tab and Combo Boxes are located under it. Before that create month list in the worksheet like the below one.

Combo in Excel - step 5

First click on Developer Tab > Insert > Combo Box > Form Controls.

Combo in Excel - step 6

Select the Combo Box to draw in the worksheet.

Combo in Excel - step 7

Now, right click on the combo box and go to Format Control.

Combo in Excel - step 8

Now the Format Control dialogue box opens up. Under Control, option do the following changes.

  • Input Range: Range of cells which contain values which will appear in your Combo Box
  • Cell link: Put here the address of the cell where will appear the number of value from the list. It will help you in creating functions of charts based on Combo Box in Excel
  • Drop Down Lines: Number of lines in Combo Box – it should be the number of values in a list or less

Combo in Excel - step 9

Now your Combo Box is ready.

Combo in Excel - step 10

How to Create an Active X Controls Combo Box in Excel?

This kind of Combo Box is used in VBA Coding. This is an advanced level of the combo box and you need a little bit of VBA knowledge. Anyway, let us go ahead and learn about this type of Combo Box.

To add Combo Box follow these steps.

  • On the ribbon click Developer Tab > Insert > Combo Box > Active X Controls. Click the deign mode on.

Combo in Excel - step 11

  • Draw the Combo Box in the Worksheet.

Combo in Excel - step 12

  • Right click on the combo box and select properties.

In this properties window, you can see all the properties associated with this Combo Box. Here you can change the name, size, font style, font color drop down list etc…

Combo in Excel - step 14

  • Change the name of the combo box.

Combo in Excel - step 15

  • Change the font type and size.

Combo in Excel - step 16

  • Select the number of rows.

Combo in Excel - step 17

  • Select Match Entry mode on.

Combo in Excel - step 18

  • Give a link to the cell. So whatever the value you select from the drop down it will reflect in that cell as a value. In this example cell is E8.

Combo in Excel - step 19

  • Exit from the Properties window and exit the design mode. That means designing of this combo box is done. If you again want to resign click on this Design Mode

Combo in Excel - step 20

Redesign Combo Box in Excel VBA

Now our Active X Control Combo Box is ready. We need to drop down the list to this excel VBA combo box through VBA code. To do this again turns on Design Mode.

  • Right click on the Combo Box and click view code and it will open the below window.

Combo in Excel - step 21

By default, VBA editor opens up. We need to add drop down values here to give access to our combo box. Follow below steps to add items to drop down the list.

  • Double Click on “This Workbook”

Combo in Excel - step 22

  • This will open up the below window.

Combo in Excel - step 23

  • Select this workbook option from the drop down

Combo in Excel - step 24

  • It will insert the automatic macro for you.

Combo in Excel - step 25

  • Now Copy and Paste the below code to.

Private Sub Workbook_Open()

With Sheets(“Active X Control”).FirstBox ‘This is our combo box name

.AddItem “January”

.AddItem “February”

.AddItem “March”

.AddItem “April”

.AddItem “May”

.AddItem “June”

.AddItem “July”

.AddItem “August”

.AddItem “September”

.AddItem “October”

.AddItem “November”

.AddItem “December”

    End With

End Sub

  • Save as Macro-Enabled Workbook, Close and reopen your workbook. If you save as normal excel workbook you cannot access to macros.

Now, the result will be as per the below image.

Combo in Excel - step 26

Thought it is difficult to understand for the first time users. You need at least a little bit of coding knowledge to get a hang of combo boxes in excel.

You can download this Combo Box in excel template here – Combo Box Excel Template

Recommended Articles

This has been a guide to Combo Box in Excel. Here we discuss how to create Form Control Combo Box and Active X Control Combo Box in excel VBA along with practical examples and downloadable templates. You may also look at these useful functions in excel –

  • List Box in Excel VBA
  • Formatting in Excel
  • Watch Window in Excel
  • How to Unhide Columns in Excel?

The post Combo Box in Excel and VBA appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.



This post first appeared on Free Investment Banking Tutorials |WallStreetMojo, please read the originial post: here

Share the post

Combo Box in Excel and VBA

×

Subscribe to Free Investment Banking Tutorials |wallstreetmojo

Get updates delivered right to your inbox!

Thank you for your subscription

×