Как сделать combobox excel

Add a list box or combo box to a worksheet in Excel

When you want to display a list of values that users can choose from, add a list box to your worksheet.

Add a list box to a worksheet

Create a list of items that you want to displayed in your list box like in this picture.

Click Developer > Insert.

Note: If the Developer tab isn’t visible, click File > Options > Customize Ribbon. In the Main Tabs list, check the Developer box, and then click OK.

Under Form Controls, click List box (Form Control).

Click the cell where you want to create the list box.

Click Properties > Control and set the required properties:

In the Input range box, type the range of cells containing the values list.

Note: If you want more items displayed in the list box, you can change the font size of text in the list.

In the Cell link box, type a cell reference.

Tip: The cell you choose will have a number associated with the item selected in your list box, and you can use that number in a formula to return the actual item from the input range.

Under Selection type, pick a Single and click OK.

Note: If you want to use Multi or Extend, consider using an ActiveX list box control.

Add a combo box to a worksheet

You can make data entry easier by letting users choose a value from a combo box. A combo box combines a text box with a list box to create a drop-down list.

You can add a Form Control or an ActiveX Control combo box. If you want to create a combo box that enables the user to edit the text in the text box, consider using the ActiveX Combo Box. The ActiveX Control combo box is more versatile because, you can change font properties to make the text easier to read on a zoomed worksheet and use programming to make it appear in cells that contain a data validation list.

Pick a column that you can hide on the worksheet and create a list by typing one value per cell.

Note: You can also create the list on another worksheet in the same workbook.

Click Developer > Insert.

Note: If the Developer tab isn’t visible, click File > Options > Customize Ribbon. In the Main Tabs list, check the Developer box, and then click OK.

Pick the type of combo box you want to add:

Under Form Controls, click Combo box (Form Control).

Under ActiveX Controls, click Combo Box (ActiveX Control).

Click the cell where you want to add the combo box and drag to draw it.

To resize the box, point to one of the resize handles, and drag the edge of the control until it reaches the height or width you want.

To move a combo box to another worksheet location, select the box and drag it to another location.

Format a Form Control combo box

Right-click the combo box and pick Format Control.

Click Control and set the following options:

Input range: Type the range of cells containing the list of items.

Cell link: The combo box can be linked to a cell where the item number is displayed when you select an item from the list. Type the cell number where you want the item number displayed.

For example, cell C1 displays 3 when the item Sorbet is selected, because it’s the third item in our list.

Tip: You can use the INDEX function to show an item name instead of a number. In our example, the combo box is linked to cell B1 and the cell range for the list is A1:A2. If the following formula, is typed into cell C1: =INDEX(A1:A5,B1), when we select the item «Sorbet» is displayed in C1.

Drop-down lines: The number of lines you want displayed when the down arrow is clicked. For example, if your list has 10 items and you don’t want to scroll you can change the default number to 10. If you type a number that’s less than the number of items in your list, a scroll bar is displayed.

Format an ActiveX combo box

Click Developer > Design Mode.

Right-click the combo box and pick Properties, click Alphabetic, and change any property setting that you want.

Here’s how to set properties for the combo box in this picture:

To set this property

Click BackColor > the down arrow > Pallet, and then pick a color.

Font type, style or size

Click Font > the . button and pick font type, size, or style.

Click ForeColor > the down arrow > Pallet, and then pick a color.

Link a cell to display selected list value.

Link Combo Box to a list

Click the box next to ListFillRange and type the cell range for the list.

Change the number of list items displayed

Click the ListRows box and type the number of items to be displayed.

Close the Property box and click Designer Mode.

After you complete the formatting, you can right-click the column that has the list and pick Hide.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник

VBA ComboBox

In this Article

This tutorial will demonstrate how to work with ComboBoxes in VBA.

ComboBoxes allow users to select an option from a drop-down menu list. ComboBoxes can be created in VBA UserForms or with an Excel worksheet. In this tutorial, you will learn how to create and manipulate ComboBoxes in VBA and in Excel worksheets.

If you want to learn how to create a Listbox, click here: VBA Listbox

If you want to learn how to create a Checkbox, click here: VBA Checkbox

Create a ComboBox in Excel Worksheet

In order to insert a ComboBox in the Worksheet, you need to go to the Developer tab, click Insert and under ActiveX Controls choose Combo Box:

Image 1. Insert a ComboBox in the Worksheet

When you select the ComboBox which you inserted, you can click on Properties under the Developer tab:

Image 2. Change ComboBox Properties

Here you can set different properties of the ComboBox. To start, we changed the attribute Name to cmbComboBox. Now, we can use the ComboBox with this name in VBA code.

Populate a ComboBox in VBA code

First, we need to populate the ComboBox with values. In most cases, a ComboBox needs to be populated when the Workbook is opened. Because of this, we need to put a code for populating the ComboBox in object Workbook, procedure Open. This procedure is executed every time a user opens the Workbook. Here is the code:

When you click on the drop-down menu, you will get 5 names to choose from (John, Michael, Jennifer, Lilly and Robert):

Image 3. Populate the ComboBox in VBA

Populate a ComboBox from a Cells Range

Another possible way to populate a ComboBox is to let a user do it. A ComboBox can be linked to the cells range. In this approach, every time a user enters a new value in the cells range, the ComboBox will update with that value.

If you want to enable this, you have to go to the Properties of the ComboBox and set the attribute ListFillRange to the cells range (in our case E2:E5):

Image 4. Populate the ComboBox from the cells range

We linked our ComboBox with the range E2:E5, where we put names we want (Nathan, Harry, George, Roberta). As a result, the ComboBox is now populated with these names:

Image 5. Populated ComboBox from the cells range

Get a Selected Item of a ComboBox in VBA

The purpose of a ComboBox is to get a users choice. In order to retrieve a users choice, you need to use this code:

The users selection is in the attribute Value of Sheet1.cmbComboBox object. This value is assigned to the variable strSelectedItem:

Image 6. Get a selected value from the ComboBox in VBA

We selected Julia in the ComboBox and executed the procedure. As you can see in Image 5, the value of the strSelectedItem is Julia, which is the value we selected. Now you can process this variable further in the code.

Clear a ComboBox

If you want to clear a ComboBox in VBA, you need to use Clear method of Sheet1.lstComboBox object. It will delete all the items from the ComboBox. Here is the code:

Notice that the Clear method does not delete the attribute ListFillRange, so it must be removed from the properties of the ComboBox beforehand.

When we execute the code, we get the empty ComboBox:

Image 7. Clear the ComboBox

Use a ComboBox in a Userform

As we mentioned, Combobox is most often used in Userforms. To explain how you can do it, we will first insert an Userform. In VBA editor, right-click on Module name, click on Insert and choose UserForm:

Image 8. Insert a Userform

To display controls for inserting, you need to enable the Toolbox. To do this, click on the Toolbox icon in the toolbar. After that, you will get the windows with all the controls available. You can click on ComboBox to create it in the Userform.

Image 9. Insert a ComboBox in the Userform

We will name the ComboBox cmbComboBox. In order to populate it with values, we need to put the following code into the method Initialize of the object UserForm:

This code triggers every time a user runs the Userform and populates the Combobox with these 5 names:

Image 10. The ComboBox with values in the Userform

If you want to get selected value from the ComboBox, you need to use the same logic for the Combobox in a Worksheet, which is explained earlier in the article.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Источник

Excel Combo Box

By Madhuri Thakur

Table of Contents

Combo Box in Excel

Combo Boxes in excel is not used for VBA Code but also for Excel spreadsheets. It is available under the Insert option of the Developer menu tab. These boxes are drop-down kind of elements that allow the user to select any value from the values listed in the Combo Box drop-down list. To select the Combo box option from Insert drop down and drop it anywhere in the sheet. And to add the values into the list, click right and select Format Control, select the input range and cell link.

Excel functions, formula, charts, formatting creating excel dashboard & others

There are two options for creating a “Combo Box” in Excel:

1. Form Controls “Combo Box”:- It comes inbuilt with excel. In this type of control box, users cannot make changes to properties.
2. ActiveX Controls “Combo Box”: – It is more versatile than form control. In this type of control box, a user can make changes to properties.

An example used for the two methods

In order to distinguish between the two combo boxes, let us look at the following examples –

How to Create Combo Box in Excel?

Initial steps before creating a Combo Box in Excel

In case the Developer tab is visible beside the View tab in the Excel Ribbon then,

• Click on Developer-> Insert-> select “Combo Box” from either Form Controls or ActiveX Controls options.

In case the Developer tab is not visible beside the View tab in the Excel Ribbon then,

• “Excel Options” dialogue box will be displayed as shown below. Click “Customize Ribbon”. Under the “Customize the Ribbon” drop-down box, select the “Main Tabs” value, selected by default, on the right panel. In the “Main Tabs” panel, select the Developer check box. Click Ok.

• You should now find the Developer tab in your Excel Ribbon.

• Create a simple list of values in the worksheet to start with, irrespective of the type of “Control Box”.

Form Controls “Combo Box” in Excel

Steps to create:

• Go to the Developer tab in the Excel Ribbon -> click Insert -> click on “Combo Box” under “Form Controls”.

• Click on the Excel Sheet at a location where you want to place the “Combo Box”. You may select the “Combo Box” and drag any of the small squares in the middle of the boundaries or circles in the corners to change the “Combo Box” size.

• Right, click on the new “Combo Box” -> select “Format Control”. The “Format Control” dialog box will appear.

• Click on the button to the right of the “Input range”.

• Select the cell range for the values in the list, which auto-populates the input box. Click on the highlighted button.

• Click OK in the “Format Object” dialog box.

Note: Drop down lines =8(by default) can be configured to show a number of items in the “Combo Box”.

• It Shows 8 number of items in the dropdown list.

Cell link – Enter cell number to display the position of a selected value in the list.

• It shows the position of months.

• Now you will be able to click on the drop-down arrow of the new “Combo Box” to see the list.

Steps to delete Form Controls “Combo Box” in Excel

• Go to Developer tab -> “Design Mode.”

• Select the Form Controls “Combo Box” and press Delete.

Point to be remembered

1. It helps the user to choose a value from the range of items.
2. In Form Control Combo Box, a user cannot make changes in the properties.

ActiveX Control Combo Box in Excel

The procedure to create ActiveX Control “Combo box” is a bit different from that of Form Control “Combo Box”.

Steps to create ActiveX Control Combo Box in Excel

1. Follow either of the 2 ways to view the “New Name” dialog box –

• Go to Formula tab -> click on “Name Manager” -> Click New in the “Name Manager” dialog box.

• Go to Formula tab -> click “Define Name”.

• Enter Name, Months. Click on the button to the right of the “Refer to” input box.

• Select the cell range which auto-populates the “Refers to” input box. Click on the highlighted button.

• Click OK in the restored “New Name” dialogue box.

• You will find a new range by the name”Months” in the “Name Manager” list.

• To create the “Combo Box”, we need to select “Combo Box” under “ActiveX Controls”.

• Click on the Excel Sheet at a location where you want the “Combo Box” to be placed. Right, click on the “Combo Box” -> click on Properties.

• A properties dialog box will appear.

• In the Properties dialog box, in the ListFillRange field, type the name of the named range, e.g. Months, that we have created above. Click on X in the upper right corner of the dialog box to close the same.

• Save the file with the “.xlsm” extension and reopen the file.

• Click on a pointer in “Combo Box” to see the list of values.

• To edit various properties, e.g. font size, color etc., make changes through a Properties dialog box.

To select/deselect “Combo Box”, click on “Design Mode” in the controls group of the Developer.

• To deselect “Combo Box”, deselect “Design Mode”.

• To select “Combo Box”, select “Design Mode”.

• Select “Combo Box” as shown in the following.

Steps to delete Active X Control Combo Box in Excel

• Go to the Developer tab, turn on “Design mode”.

• Select the Active X control combo box that you want to delete and press Delete.

Things to Remember

• It is more versatile.
• It allows the user to type the value in the text box from the range of items.
• In ActiveX Control “Combo Box”, the user can edit properties, e.g. font size, colors, etc.
• It is commonly used as a user interface control where users can select an item as per their own choice.
• It makes data entry easier and has many features and flexibility.

Recommended Articles

This is a guide to Combo Box in Excel. Here we discuss its uses and how to create Combo Box in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in excel –

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

Источник