Combo box - form control in MS EXCEL
A combo box is a combination of a text box and a drop-down list. A combo box is smaller than a regular list box, but the user must click an arrow to display a list of items. A combo box should be used when you want to be able to enter or select only one item in a list. This control displays the current value. This element has much in common with the element.
To insert controls on a sheet, you must display a tab Developer.
- In MS EXCEL 2007, this can be done through the menu Office button/ Excel Options/ General/ Show Developer tab on the ribbon.
- In MS EXCEL 2010, this can be done like this: Open the tab File; Click the button Options; Click the button Customize Ribbon; Choose a team Ribbon Customization and Main Tabs check the box Developer.
Now you can insert a control through the menu: .
Note that it is also possible to insert ActiveX Controls from this menu, which are located below the Form Controls we are interested in. Both types have the same elements Button, etc. The difference between the two is that you need to use VBA to use ActiveX Controls, while Form Controls can be directly bound to a cell on a sheet.
combo box(Combo box, Drop down) as well as all other form controls, returns only 1 numeric value. See example file.
An overview article about all form controls is available.
Insert Combo Box
Through the menu Developer/ Controls/ Paste select the element with the left mouse button combo box(see picture below).
After that, the drop-down menu will close, and the cursor, instead of the usual thick cross
will turn into a thin cross.
Hold down the left mouse button and move the cursor to the right and slightly down, the element combo box will be placed on the sheet.
Combo box selection
To highlight combo box press and hold the key CTRL, then left click on combo box.
Moving and Resizing a Combo Box
If you hover over dedicated element combo box(the cursor will take the form of 4 arrows directed in different directions), then press and hold the left mouse button, then you can move it. can be aligned combo box along the borders of the cells. The selected element can also be moved using the keyboard arrows.
If you hover over the corners of the rectangle or the small circles on the border, you can change its size.
Populate the combo box with elements
Let's fill in our combo box the names of the months. First, let's place the names of the months on the sheet in the range F2:F13 .
To fill in combo box, click on it with the RIGHT mouse button, in the context menu that appears, select Object Format...
Enter in the field Build list by range link to the above range.
Click OK combo box filled with elements.
Now the user can select the desired month, but combo box is not yet able to return the selected value to the cell. About how to tie combo box with cell read below.
Associating a combo box with a cell
As mentioned above, all form controls return a value. This value is placed in a cell defined by the user. To link a control to a cell, click on it with the RIGHT mouse button, in the context menu that appears, select Object Format... A dialog box will appear, select the Control tab (if there is no such tab, then you have inserted an ActiveX Control, not a Form Control, see above).
In field Cell communication you need to enter a reference to the cell. Let's tie our combo box with cell A1 .
Click OK.
There is another way to link a Control and a cell: right-click the Control, type =, then left-click on the desired cell, press the key ENTER. To change the cell to which the control is associated, just drag this cell to the desired location by dragging its border.
Using a Combo Box
combo box convenient for selecting a single value from a pre-prepared list. The selected value can be used to output corresponding values from the same row. For example, if the sales table contains sales volumes by month (range F2:G13 in the figure below), then choosing in combo box month, you can display the corresponding sales volume (see cell AT 3 ).
It must be remembered that combo box returns to the linked cell not the element itself, but its position in the list (for the month of May in the picture above combo box returned the value 5). Therefore, to display the selected month, you need the formula =INDEX(F2:F13;B1) (cell AT 2 ).
The formula =INDEX(G2:G13;B1) allows you to display the sales volume for the selected month (cell AT 3 ).
Control Name
Each Control has a name. To find out, you need to select combo box, its name will be displayed. To change the name Combo boxes- enter in Field name new name and press the key ENTER. You can also change the name in Selection areas (Home / Editing/ Find & Select/ Selection Area).
Why do we need to know the name of the control? If you do not plan to drive Combo boxes from a VBA program, the name may be required only to customize its display on the worksheet. Read about it below.
Hiding a combo box on a sheet
Turn on Selection area (Home / Editing / Find and Select)
IN Selection areas You can control the display of not only Controls, but also other objects on the sheet, such as pictures.
Click on the eye image next to the name of the object and the object will disappear / appear.