Drop-Down Lists, Buttons, and Other Controls

Drop-Down Lists, Buttons , and Other Controls

You can add controls to your spreadsheets, such as drop-down lists and buttons, that are attached to macros that were recorded or written in StarBasic or JavaScript. You're only limited by your imagination in how you can use controls in your spreadsheets.

This sections describes two possibilities for using controls in spreadsheets: adding a drop-down list that lets you select from a predefined list of values to populate a cell ; and adding buttons that let you sort spreadsheet columns in ascending or descending order by clicking them.

Even though these sections give you the specific procedures for implementing these controls, the principles and mechanics are the same for setting up controls using other scenarios.

For full details, see Chapter 36, Creating and Using Forms, Controls, and Events , on page 921.

Graphics and Drawings

The procedures for adding graphics and drawings to spreadsheets are similar to those used in Writer. See Inserting Graphics on page 274.

Note

If you select Link and the file is on a Web page or you haven't sent out the graphics to everyone you sent the spreadsheet too, you'll have problems. See Think Before You Link on page 696.

Mathematical Formulas

The procedures for adding formula objects to spreadsheets are similar to those used in Writer. See Mathematical Formulas on page 300.

Floating Frames

The procedures for adding floating frames to spreadsheets is similar to those used in Writer. See Using Frames for Advanced Page Layout on page 239.

Updating Links to Graphics and Objects

Whenever you add an object to a spreadsheet as a link, such as a graphic file you want to update in the spreadsheet when the file changes, determine how the link is updated.

Automatically Updating Links

To have Calc automatically update links to files in a spreadsheet, choose Tools > Options > Spreadsheet > Other. Then select the Always option.

If you select On request, Calc prompts you to update the links whenever you open the document. If you select Never, Calc opens the document without updating the links, and you need to update them manually.

Manually Updating Links

If you don't have Calc set up to update links automatically, Choose Edit > Links. This menu option is available only if you've inserted an object in the spreadsheet by link. In the Edit Links window, select the link you want to update, and click the Update button.

Chapter 22. Useful Spreadsheet Tools

IN THIS CHAPTER

Protecting Cells From Modification
Controlling Valid Entries
Using Scenarios
Using Goal Seek
Outlining
Naming Spreadsheet Areas
Pointing to Cell References and Errors
Value Highlighting
Conditional Formatting

"-->

Protecting Cells From Modification

You can protect any and all cells in a spreadsheet from being modified in any way. When someone clicks a protected cell, the program displays a message saying that the cell can't be modified.

Cell protection is useful when you want to protect calculated amounts, protect cells containing formulas you painstakingly created, or to help guide data entry. In short, cell protection helps make your spreadsheets dummy -proof.

There are two aspects to the cell protection process, as illustrated in Figure 22-1. First, each cell has a "Protected" option you can select or deselect. Second, you must turn cell protection on from the Calc menu, which protects all cells that have their Protected option selected.

Figure 22-1. The two aspects of cell protection

graphics/22fig01.jpg

By default, all cells in a spreadsheet have the Protected option selected. Because you may want to protect only a handful of cells, you may find it easier to turn cell protection off for all cells, then go back and select only the cells you want to protect. This procedure guides you through this process.

The procedure assumes that the cells you want to protect already contain the data you want in them and are formatted the way you want them.

Click the gray box above row 1 and left of column A to highlight the entire spreadsheet.
Right-click in the spreadsheet and choose Format Cells.
In the Cell Attributes window (Figure 22-1), select the Cell Protection tab and deselect the Protected option.
Click OK.
In the spreadsheet, select the cells you want to protect. See Selecting Non-Adjacent Cells on page 535.
Right-click one of the selected cells and choose Format Cells.
In the Cell Protection tab, select the Protected option.
Click OK.
Choose Tools > Protect Document > Sheet to protect the sheet. If you've set up cells for protection on multiple sheets, choose Tools > Protect Document > Document to protect all sheets.
In the Protect Sheet (or Protect Document) window that appears, you can set a password that applies to unprotecting the cells.

If you don't want to require a password for unprotecting the cells, don't enter a password in this window. Just click OK.

If you forget your password for unprotecting sheets, you're out of luck. You have to live with the cell protection. You can't even delete a protected sheet. You can, however, copy a protected cell to and paste it into an unprotected cell, where it will become unprotected .

Protected cells can still change format with conditional formatting if the conditional formatting was applied before the cells were protected.

Buy on amazon.com >>

Haugland S., Jones F.

<< Previous page

Table of contents

Next page >>