Setting Combo Box to load items
To set the combo box to that when we click on it, a list will appear and we can select an item from the list. In this case, we want to select a customer name from it. This requires us to have a sheet which contain a list of customers, let's say in sheet CUSTOMERS and the list is in the range of say A2 to A100. We want to make the list of customers in sheet CUSTOMERS appear in the combo box. Here is how:
Right-click on the combo box. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Input Range, enter the address where the list of customers is located (in this case, it is CUSTOMERS!A2:A100).
Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Input Range setting to take effect. Now if you click on the combo, a list will appear and you can click on one item from the list to select it. The list will disappear and the selected item will appear in the combo.
Show more than 8 items
When you click on the combo, by default only 8 items will be visible at a time. You can set to display more. Right-click on the combo once. A floating menu will appear. Select Format Control To open the Format control dialog box. Click on tab Control. In the box on the right of label Drop Down Lines, enter a number, say 20. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Drop Down Lines setting to take effect. Now if you click on the combo, the number of items that appear is no longer 8 but 20.
No printing of Combo box
Usually, we don't want the combo box to get printed when we print the sheet because it is ugly. The steps to make it non-printable is as follows:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Properties. In the box on the left of label Print Object, if there is a check-mark, remove it by clicking it once. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Print Object setting to take effect. Now whenever you print the sheet, the combo and its content will not get printed.
Linking Combo box to a cell
In this example, we will use cell Al as a Link Cell to the Combo Box. We will set it so that when we select an item from the combo box, cell A1 will show the position of the item as it appears in the list in the combo box. This position is also pointing to the position of the item in the list in sheet CUSTOMERS. This position number is useful for the formula in next paragraph. To set cell A1 as the Link Cell, the steps are:
Right-click on the combo once. A floating menu will appear. Select Format Control to open the Format control dialog box. Click on the tab Control. In the box on the right of label Cell link, enter: A1. Click OK. The dialog Control will disappear. You will come back to the sheet where the combo is present. Currently the focus is on the combo. Click at any cell to take the focus away from the combo. This is to allow our Cell link setting to take effect. Now if you click on the combo and select an item, the position of the item in the list will appear in cell A1.
No comments:
Post a Comment