Add Combo Box to Sheet in Microsoft Excel

Tuesday 26 October 2010 by nkoknki | 0 comments

--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.

Example:

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

Enter the months list - January-December into the cells A1 to A12 in the new worksheet.
Select cells A!:A12, press Ctrl+F3, enter the text MonthsList into the Names In workbook box, and click OK.
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

Select one of the toolbars, right-click, and select the Forms toolbar.
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

Select the Combo Box; right-click; and from the shortcut menu that appears, select Format Control. Then select the Control tab
In the Input range box, type the name MonthsList (You cannot press F3 to paste a name with an object).
In the Cell link box, type the name MonthNumber.
Click to select the 3-D shading box (more aesthetic).
Click OK.

--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, select Cut.
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