» Add Combo Box to a Sheet in Microsoft Excel
What are the Advantages of working with Combo Box?
Choosing an item from a list in the Combo Box
returns a number. The number then replaces the use of the Match formula
to calculate the column number in the Vlookup formula (read
tip) and replaces the use of the Match formula (read
tip) to calculate the column number in the Index formula.
Working with the Combo Box
A list of the cell ranges in the sheet is attached
to the Combo Box. After an item is selected from the list, the Combo
Box enters into the linked cell the ordinal number of the item that
is selected on the list.
The screen shot displays a profit and loss statement
as compared to the previous year. Changing the month in the Combo Box
will automatically change the display of data according to the selected month.
Step 1: Define names
the months list - January-December into the cells A1 to A12 in the new
cells A!:A12, press Ctrl+F3, enter the text MonthsList
into the Names In workbook box, and click OK.
3.Select Cell B1, press Ctrl+F3, enter the text MonthNumber
into the Names in Workbook box, and click OK.
Step 2: Add a Combo Box to a worksheet
1. Select one of the toolbars, right-click, and select the Forms
2. Copy the Combo Box by clicking the Combo Box
icon, and then release the mouse. Create a rectangle with
the mouse in the worksheet, and then release the mouse.
Step 3: Format the Combo Box
1.Select the Combo Box
; right-click; and from the shortcut menu
that appears, select Format Control
select the Control
2.In the Input range box, type the name MonthsList
(You cannot press F3 to paste a name with an object).
3.In the Cell link box, type the name MonthNumber.
4.Click to select the 3-D shading box (more aesthetic).
Exit the formatting mode of Combo Box, and select a cell in the sheet.
Open the list of items in the Combo Box, and select a month. Note that
the new month number is shown in cell B1.
Advantages of working with names in a Combo Box
Attaching a list with Define Names causes your list references to
be updated automatically in the Combo Box. Sorting the list in the sheet
will automatically sort the list in Combo Box as well.
Deleting a Combo Box
Select the object; right-click; and from the shortcut menu that appears,
Adjusting the size of the Combo Box to the cells
Select the Combo Box and right-click; the
Combo Box is now in editing mode. Adjust the width or height of the Combo
Box by dragging one of the corner boxes; adjust the placement of the Combo
Box on the worksheet by clicking anywhere inside the box and dragging it.
Automatic adjustment of the Combo Box
Hold down the Alt key while you click the
mouse to adjust the size of the Combo Box.
|Screenshot // Add Combo Box to a Sheet in Microsoft Excel|