How to Create a Dynamic ComboBox in Excel Using Multipage and Frame Controls

A ComboBox is a control that allows the user to select an item from a drop-down list or type a new value. A Multipage is a control that allows the user to switch between different pages of controls within a userform or a worksheet. A Frame is a control that acts as a container for other controls and can have a caption and a border. A Worksheet is a sheet in an Excel workbook that contains cells, formulas, and data.

To create a ComboBox inside a Multipage inside a Frame inside a Worksheet, you need to follow these steps:

1. Enable the Developer tab in Excel by clicking on File > Options > Customize Ribbon and checking the Developer box in the right pane.
2. Click on the Developer tab and then click on Insert > ActiveX Controls > More Controls.
3. Scroll down the list of controls and select Microsoft Forms 2.0 Frame. Click OK and then draw a frame on your worksheet.
4. Right-click on the frame and select Frame Object > Edit. This will activate the Toolbox, which contains various controls that you can add to the frame.
5. From the Toolbox, select the Multipage control and draw it inside the frame. You can adjust the size and position of the multipage as needed.
6. Right-click on the multipage and select Properties. This will open the Properties window, where you can change various settings of the multipage, such as the number of pages, the captions, the colors, etc.
7. To add a ComboBox to the first page of the multipage, select the ComboBox control from the Toolbox and draw it on the page. You can also adjust its size and position as needed.
8. Right-click on the combobox and select Properties. This will open the Properties window, where you can change various settings of the combobox, such as the name, the list style, the list fill range, the match entry, etc.
9. To fill the combobox with a list of values from a range on another sheet, enter the address of the range in the ListFillRange property. For example, if you have a list of fruits on Sheet2 in the range A1:A5, you can enter Sheet2!A1:A5 in the ListFillRange property.
10. To exit the design mode, right-click on the frame and select Frame Object > Exit Edit. This will deactivate the Toolbox and the Properties window and allow you to interact with the controls on the worksheet.

Here is an example scenario of how you can use the combobox inside the multipage inside the frame inside the worksheet:

• Suppose you have a worksheet that contains sales data for different products in different regions. You want to use the combobox to filter the data by product and display the total sales for each region in a table.
• To do this, you need to create a formula that will sum the sales values based on the product selected in the combobox. You can use the SUMIFS function to do this. The syntax of the SUMIFS function is:

  $$\text{SUMIFS}(sum\_range,criteria\_range1,criteria1,[criteria\_range2,criteria2],\dots )$$

  where sum_range is the range of cells that you want to sum, criteria_range1 is the range of cells that contains the first condition, criteria1 is the first condition that you want to apply, and so on. You can have up to 127 pairs of criteria ranges and criteria in the SUMIFS function.

• In this example, the sum_range is the range of cells that contains the sales values, criteria_range1 is the range of cells that contains the product names, and criteria1 is the value of the combobox. You can refer to the value of the combobox by using its name, which is ComboBox1 by default. You can change the name of the combobox in the Properties window if you want.
• To display the total sales for each region in a table, you can use the following formula in the cells below the region names:

  $$\text{SUMIFS}(D2:D21,C2:C21,ComboBox1)$$

  where D2:D21 is the sum_range, C2:C21 is the criteria_range1, and ComboBox1 is the criteria1. This formula will sum the sales values in column D that match the product selected in the combobox in column C.

• You can copy this formula to the other cells in the table and format the table as you like. You can also add a grand total row at the bottom of the table by using the SUM function.
• To test the combobox, you can select a different product from the drop-down list or type a new value and press Enter. The table will update automatically to show the total sales for each region based on the product selected.

Here is an example of how the worksheet might look like:

Here is an example of how the combobox might look like:

Another approach to create a combobox inside a multipage inside a frame inside a worksheet is to use the Forms controls instead of the ActiveX controls. The Forms controls are simpler and more compatible with different versions of Excel, but they have fewer features and customization options than the ActiveX controls. To use the Forms controls, you need to follow these steps:

1. Enable the Developer tab in Excel by clicking on File > Options > Customize Ribbon and checking the Developer box in the right pane.
2. Click on the Developer tab and then click on Insert > Form Controls > Group Box. Draw a group box on your worksheet and enter a caption for it.
3. Click on the Developer tab and then click on Insert > Form Controls > Multipage. Draw a multipage inside the group box and adjust its size and position as needed.
4. Right-click on the multipage and select Format Control. This will open the Format Control dialog box, where you can change various settings of the multipage, such as the number of pages, the captions, the colors, etc.
5. To add a combobox to the first page of the multipage, click on the Developer tab and then click on Insert > Form Controls > Combo Box. Draw a combobox on the page and adjust its size and position as needed.
6. Right-click on the combobox and select Format Control. This will open the Format Control dialog box, where you can change various settings of the combobox, such as the input range, the cell link, the drop-down lines, etc.
7. To fill the combobox with a list of values from a range on another sheet, enter the address of the range in the Input range box. For example, if you have a list of fruits on Sheet2 in the range A1:A5, you can enter Sheet2!A1:A5 in the Input range box.
8. To link the combobox to a cell on the worksheet, enter the address of the cell in the Cell link box. This will make the cell display the index number of the item selected in the combobox. For example, if you want to link the combobox to cell A1, you can enter A1 in the Cell link box. This will make cell A1 display 1 if the first item in the combobox is selected, 2 if the second item is selected, and so on.

The rest of the steps are similar to the previous approach, except that you need to use the INDEX function instead of the SUMIFS function to refer to the value of the combobox. The syntax of the INDEX function is:

$$\text{INDEX}(array,row\_num,[column\_num])$$

where array is the range of cells that contains the values you want to return, row_num is the row number of the value you want to return, and column_num is the column number of the value you want to return. You can omit the column_num argument if the array has only one column.

In this example, the array is the range of cells that contains the product names, row_num is the value of the cell linked to the combobox, and column_num is omitted. You can refer to the value of the cell linked to the combobox by using its address, which is A1 by default. You can change the address of the cell linked to the combobox in the Format Control dialog box if you want.

To display the total sales for each region in a table, you can use the following formula in the cells below the region names:

$$\text{SUMIFS}(D2:D21,C2:C21,\text{INDEX}(C2:C21,A1))$$

where D2:D21 is the sum_range, C2:C21 is the criteria_range1, and INDEX(C2:C21, A1) is the criteria1. This formula will sum the sales values in column D that match the product selected in the combobox in column C.