Using Combo Boxes with OpenOffice Calc
How to Add a Combo Box to an OpenOffice.org Calc Spreadsheet
Aug 5, 2008
OpenOffice.org Calc spreadsheets are incredibly useful - whether you're creating a business plan for your company or just working out your monthly finances at home. However, it doesn't take long until what starts out as a simple set of calculations becomes very complicated - which can be very intimidating for the new user; and that's when they need all the help that they can get: so that's also when they need a combo box adding to the Calc Spreadsheet.
What is a combo box?
A combo box is a very neat way of displaying lists of data - instead of displaying the data in columns, the data is displayed in a drop down box; this combo box will contain a list of information (for example a list of names) but the list is only displayed if the user clicks on the combo box itself. The user may then select one of the items in the list to be used elsewhere in the Calc spreadsheet.
Adding a Combo Box to a Calc Spreadsheet
Adding a combo box to a Calc spreadsheet couldn't be easier; it's just a matter of doing the following:
- click on View
- click on Toolbars
- tick Form Controls
- click on the combo box icon in the Form Controls toolbox
- use the mouse pointer to draw a combo box on the Calc sheet
- cancel the Combo Box Wizard (which is used to obtain the list from a database)
Once the combo box has been created on the Calc spreadsheet then its list of items can be created, and items selected by the user can be used elsewhere in the spreadsheet,
Assigning a List of Values to the Combo Box
The list of values used in an OpenOffice combo box is actually stored in a column somewhere in the spreadsheet (for example in the range H1 to H5 in Sheet2); therefore the user needs to:
- populate the range
- tell the combo box about the range of data.
Obviously the first task would be carried out by the user typing the list into the cells that make up the range; however, the second task is achieved by the user:
- right mouse clicking on the combo box
- selecting Control (which will show the combo box properties dialog box)
- clicking on the Data tab
- selecting the Source cell range and entering the address of the range of cells containing the list (for example Sheet2.H1;H5)
With the source for the list defined the user must now select the linked cell - the target cell for any user selections made with the combo box.
Defining the Linked Cell for the Combo Box
When a user selects an item from the combo box then the selected data is stored in a cell on the Calc spreadsheet - that cell is the Linked Cell. Just like the source cells the linked cell is defined in the properties dialog for the combo box; and so, if the combo box selection is to be stored in cell A1, then A1 needs to be entered as the linked cell.
Using the Combo Box
At the moment the combo box is in design mode - enabling the user to make any necessary changes; to see the combo box in action the user must click on the Design Mode On/Off icon in the Form Controls toolbox - all of the icons will be greyed out, and the combo box will now be active.
The end result is that:
- if the user clicks on the combo box then their defined list (from the source cells) will be displayed
- if the user selects one of the items in the then this selection will be automatically loaded into the linked cell
