Using Grids with Protected Worksheets

XLCubed Grids can coexist with protected worksheets in several ways.

Using the Grid on the protected worksheet

The Grid may be placed directly on the worksheet that is to be protected. To do this, follow these steps:

• On the Grid Properties > Appearance tab, deselect Apply formatting and Merge repeating cells
• Set the Grid cells to be unlocked. See this Microsoft article for details on how to lock and unlock specific cells.
• If the Grid can be edited by drilling or other member selection methods, also unlock cells to accommodate the potential expansion.
• Protect the sheet. The following options must be allowed:
  • Format cells
  • Format columns
  • Format rows

If Format columns and Format rows protection are desired, then Grid Properties > Behaviour > Resize columns/rows after refresh must be disabled.

Placing the Grid on a non-protected worksheet

The Grid can be placed on a non-protected worksheet, and the data brought to the protected sheet by using either:

• The Camera tool (see this post for details)
• Formulae

Alternatives

• Publishing the finished reports
• Using Formulae instead of Grids

See Also

• Lock or unlock specific areas of a protected worksheet