416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

Excel Styles (Home), Excel Styles are Additive , Hand-Crafted Styles, Implementing Protection , Style History

Implementing Protection

Specific areas of our workbooks must be protected/unprotected, and we would like to implement protection with our styles.

The general process is to mar all cells as LOCKED except those for user-input, and then to protect the worksheets.

Create a style called “UserInput”.

Only the Patterns and Protection tabs will be implemented.

Set the Pattern to be pale yellow and turn Locked to OFF in the Protection tab.

Create a style called “Calculation”.

Only the Patterns and Protection tabs will be implemented.

Set the Pattern to be bright yellow and turn Locked to ON in the Protection tab.

Protect the worksheet and establish that the User Input cells can be changed, but the Calculation cells cannot be changed.

Now consider numeric formatting.

Create a style called Number2 whose sole purpose is to display the cell contents to two decimal places.

Only the Patterns and Protection tabs will be implemented.

Set the Pattern to be bright yellow and turn Locked to ON in the Protection tab.

With the sheet protected, and with a single style applied to each cell, everything works as we would expect.

Only the two User Input cells can be changed.

Satisfy yourself that application of styles is not only additive but is commutative:

In rows 5 and 6 in the screenshot above, I applied the Number2 style to the four cells, and then applied User Input and Calculation.

In rows 8 and 9 in the screenshot above, I applied the User Input and Calculation styles, and then applied the Number2 style over all four cells.

The sequence in which the styles are applied makes no difference.

Excel’s default setting is to have locked set ON in the Protection tab.

Loading

Toronto and Mississauga, Sunday, December 05, 2010 3:08 PM

Copyright © 1996-2010 Chris Greaves. All Rights Reserved.