Microsoft Excel security, protect, encrypt, restrict access to spreadsheets, VBA code

Microsoft Excel security, protect, encrypt, restrict access to spreadsheets, VBA code

Microsoft Excel Security Tips


Protecting Cells Containing Formulas in an Unprotected Sheet

To protect cells containing Formulas in an unprotected sheet, use Validation:

Step 1: Selecting cells containing formulas:

  • Press F5.
  • Click Special, and then select the Formulas option.
  • Click OK.

Step 2: Validation:

  • From the Data menu, select Validation.
  • Select the Settings tab, and select Custom from the Allow dropdown list.
  • In the Formula box, type ="", and then click OK.

Prevent Unauthorized Opening of Workbook

To enter a password:

  • From the File menu, select Save as.
  • From the Tools dropdown list, select General Options.
  • In the Save Options dialog box, enter passwords into Password to open
    and Password to modify boxes, and click OK.

Password to prevent opening a workbook in Microsoft ® Excel

For versions of Microsoft ® Excel 2002, 2003 and 2007, you can prevent the opening
of a workbook by adding a password in the Security tab.

From Tools choose Options, Choose Security tab and add a password of your choice.

Protecting the workbook structure

Why it is important to protect a workbook's structure:

  • To prevent the structure from being changed.
  • To prevent sheets from being deleted.
  • To prevent new sheets from being inserted.
  • To prevent hidden sheets from being opened.

To protect the workbook structure:

  • From the Tools menu, select Protection and then Protect Workbook.
  • Type a password in the Password box, and click OK.
  • Confirm the password, and click OK again.

Protecting the Sheet or the Cells in the Sheet

Protecting the sheet means that the content of cells cannot be changed
and/or formulas can be hidden from view.

To protect a sheet:

  • From the Tools menu, select Protection, and then Protect Sheet.

To protect the cells in the sheet using the Protect Sheet option:

  • Press Ctrl+1, and select the Protection tab in the Format Cells dialog box.
  • Select the Locked checkbox and click OK.
  • Protect the sheet (see above). To protect formulas in cells.

To hide text/formula in the Formula bar:

  • Press Ctrl+1, and select the Protection tab in the Format Cells dialog box.
  • Select the Hidden checkbox and click OK.
  • Protect the sheet (see above).

Restrict the accessible area by hiding rows and columns.

To protect data in cells by hiding rows and columns:

  • Select a column. For example, select column E and press Ctrl+Shift+Right Arrow.
  • Press Ctrl+0, or press Shift+F10 and select Hide from the shortcut menu.
  • Select a row. For example, select row 32 and press Ctrl+Shift+Down Arrow.
  • Press Ctrl+ 9, or press Shift+F10 and select Hide from the shortcut menu.

Result: The unrestricted area is the visible area, range A1:D31.

To unhide rows and columns:

  • Select row 31, and while placing the cursor on the row number, click and drag it
    slightly downward into the non-accessible area.
  • Release the mouse button and return the cursor to row 31.
  • Press Ctrl+Shift+(, or press Shift+F10 and select Unhide from the shortcut menu.
  • Select column D, and while placing the cursor on the column number, click and drag it
    slightly to the right into the non-accessible area.
  • Release the mouse button and return the cursor to column D.
  • Press Ctrl+Shift+), or press Shift+F10 and select Unhide from the shortcut menu.

Preventing Movement in Protected Areas in Microsoft ® Excel

You can divide the area of a sheet containing data into two parts: an area where
movement is unrestricted (scroll area) and an area where movement is restricted,
meaning that it is protected.

You can set the scroll area in a sheet either by using the Scroll Area macro or by
making a change in the sheet's Properties dialog box.


Changing properties in the Properties dialog box

To change the sheet's properties, you need to open the macro editor, VBE.

  • Press Alt+F11.
  • Under VBA Project, select the sheet for which you want to change the Scroll Area
    property (You may need to hit Ctrl+R to display VBA Project).
  • Under Properties, select the Scroll Area cell (see the screen shot), and type the
    reference of the range you want to set as the scroll area. In the screen shot,
    note that the reference given is $A$1:$C$15.
  • To close the VBE or return to Excel, press Alt+Q.

The result: in the range A1:C15, you can perform any action in the cells. In all the other cells
in the sheet, you are restricted to viewing the cells and cannot move or scroll between them.

Allowing Multiple Users to Edit Ranges in Microsoft ® Excel

To allow users to only edit specific ranges when working in a shared workbook:

From the Tools menu, select Protection, and then Allow Users to Edit Ranges.

This advanced option allows multiple users (for example, when working on a network)
to update data in a well defined and private area. Each workbook user is allotted a range
in the sheet with a unique password.

Preventing Hidden Sheets from Appearing

When a sheet is hidden and the workbook is not protected, it will still appear in the list
of hidden sheets in the Unhide box (from the Format menu, select Sheet, and then Unhide).

To prevent hidden sheets from appearing in the Unhide box:

  • Click the Properties icon OR from the Control Toolbox toolbar, click the Properties icon.
  • From the Visible dropdown list, select xlSheetVeryHidden.

To cancel the xlSheetVeryHidden option for a hidden sheet:

  • Press Alt+F11 to open the VBE.
  • Under VBAProject in the left pane, double-click the sheet name you want to unhide.
  • Press F4 or click the Properties icon to open the sheet's Properties dialog box.
  • In the Visible dropdown list, select xlSheetVisible.
  • Press Alt+F4 to close the VBE.

Protecting Workbooks with a Digital Signature

Microsoft ® Excel 2002, 2003 and 2007 offer a new Digital Signature feature that ensures
a higher level of security.

To set a Digital Signature:

  • From the Tools menu, select Options, and then select the Security tab.
  • Click Digital Signatures. In the Digital Signature dialog box, add the signer's Digital Signature.

Protecting Workbooks with a Digital Signature in Excel 2007

To set a Digital Signature:

  • Select File /Finish / Add a Digital Signature.
  • To get a digital ID or create your own digital ID click OK, or use Signatures Services
    from the Microsoft ® Office Marketplace.