Using Grids with Protected Worksheets

XLCubed Grids can coexist with protected worksheets in several ways.

Set the XLCubed Workbook Options to use your password

In the XLCubed > Options > Workbook Options... dialog, you can set the Workbook > Workbook protection password. The Grids should then update as required.

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 all 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. At least 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

• Password protect worksheet or workbook elements
• Lock or unlock specific areas of a protected worksheet