Protecting Layout and Formulas

While one of Excel offers users the ability to interact with and modify any area of a spreadsheet, there are times where you may not want users of your spreadsheet application to inadvertently change contents. The ability to protect certain parts of a spreadsheet allows for full control, while providing users with the features and familiarity of Excel.

DifficultyIntermediate
Time Est.20-40 Minutes
VBA/MacrosYes
Template Files2016-10-30-securing-layout-and-formulas-template
Solution Files2016-10-30-securing-layout-and-formulas-solution (PWs: 123456)

Spreadsheet Design Exercise 11

Today’s exercise within the Spreadsheet Design series, finishes the focus on Layout. The focus will be on protecting layout and formulas. Excel has three primary protection capabilities. As a spreadsheet developer, you can protect the workbook, each spreadsheet in the workbook, and the VBA Editor.

In this exercise, you will learn how to properly protect the contents of the stock purchase record spreadsheet, which was developed in prior exercises (Modifying Data in Excel with VBA, Building a Custom Listbox in Excel with VBA, Adding New Data to Datasets in Excel). The template can be downloaded here: 2016-10-30-securing-layout-and-formulas-template.

Step 1: Add workbook open event.

Open the VBA Editor and enter the following code into the Workbook object.

This script will tell the workbook to protect the user interface only, preventing the worksheet protection from interfering with VBA scripts. That is, any user inputs, such as keyboard or mouse clicks, will not be permitted on any locked cells. One alternative to this method is incorporate the Worksheet.Unprotect method at the beginning of each of each subroutine, and the Worksheet.Protect method at the end of each subroutine. This approach can be a bit cumbersome, however, and the suggested method above only requires one line of code.

Note that once the above script is saved, your worksheet will be protected if you close and re-open. Make sure you write down the password you set. If your worksheet is locked, you will need to unprotect it to complete this exercise.

Step 2: Unlock cells with a data

Select the cell range where the user will need to input data. In the template provided, that range is C11:H11. Right click on the selection, then select Format Cells in the menu.

2016-10-30-1
Right click on the input cell range.

The Format Cells dialog box will appear, opening to the Number tab.

2016-10-30-2
Format Cells dialog box.

Select the (1) Protection tab, then uncheck the (2) first checkbox labeled as “Locked”. This option exclude the selected cells from the “Locked” treatment and will enable users to input data into the cell when the while the worksheet is locked.

Step 3: Set the behavior of locked cells.

Now that input cells have been specified and unlocked, the worksheet can now be locked.

2016-10-30-3
Password protection options.
  1. Select Review.
  2. Select Protect Sheet.
  3. Enter the same password you entered in the script from step 1.
  4. Select treatment of locked cells.

The “Allow all users of this worksheet to” listbox provides for flexibility in the treatment of a locked spreadsheet. By default, Selected locked cells and Select unlocked cells will be checked. None of the other options below will be checked initially. It’s a developer’s preference whether to let users select locked cells. Preventing the selection of locked cells gives a spreadsheet more of an application feel, but sometimes may frustrate users as they may like select cells, even if they cannot edit them.

The following error message appears if a user attempts to edit a selected locked cell:

2016-10-30-4
Error message when attempting to edit locked cells.

users are returned to the spreadsheet after pressing OK.

Required setting for the template:

Given that the modify macro built in the previous exercise relies on users being able to select locked cells, it’s important for users to be able to select locked cells for this exercise. With that, keep the protection settings at their default. You can restrict users from selecting locked cells on any worksheets that do not have a worksheet SelectionChange event.

Step 4: Test worksheet.

With the worksheet now protected, quickly test functionality to make sure all operations can run as expected. If you run into any error messages, you may need to alter a cell’s protection settings.

When working on your own projects, keep in mind that cells influenced by form controls will need to be unlocked. There may be other unintended consequences as well, so thorough testing of all features and possibilities is essential.

Step 5: Hide operational and data worksheets.

A good general practice is to hide any unnecessary worksheets. This goes along with the discussion during this series navigation part of making one-tab spreadsheet applications. Many are familiar how to hide/unhide worksheets in the worksheet tab toolbar, but we will do this through the VBA editor to access a third option.

2016-10-30-5
Access the VBA Editor.

Open the VBA Editor by selecting (1) Developer > (2) Visual Basic. If you do not see the Developer tab, you can click here (coming soon) to find instructions on how to activate it.

While in the VBA Editor, select Sheet 2 (Data)

2016-10-30-6
VBA Editor window.

then press the F4 key on your keyboard. The Properties dialog box will appear in the lower left of the VBA Editor.

Properties dialog box opens when pressing the F4 key.
Properties dialog box opens when pressing the F4 key.

The bottom of the Properties dialog box has an option for Visible. This will allow you to set the visibility level for the user.

2016-10-30-8
Sheet visibility options.
  • -1 – xlSheetVisible: The worksheet is fully visible as a tab in the tab toolbar.
  • 0 – xlSheetHidden: The worksheet will be hidden in the toolbar, but it can be unhidden by the user if the user left clicks on the toolbar.
  • 2 – xlSheetVeryHidden: The worksheet will be hidden in the toolbar and the user will be unable to unhid the worksheet from the worksheet tab toolbar.

If there’s no reason for a user to meddle around in other worksheets, the best option is to use the xlSheetVeryHidden option.

Repeat this step for Sheet3 (Operations).

Step 6: Protect the VBA editor.

Within the VBA editor menu, select Tools > VBAProject Properties.

2016-10-30-9
VBAProject – Project Properties dialog box.

In the VBAProject – Project Properties dialog box:

  1. Select the Protection tab.
  2. Within the Lock project section, activate the checkbox for Lock project for viewing.
  3. Enter a password, then confirm password. Use a longer than normal password.

After pressing OK, close, save, then re-open the workbook. This will activate the VBA editor protection.

Step 7: Final Test

As a final step, check to make sure the lock on the worksheet and VBA editor was properly set. Also, right click on the worksheet tab toolbar to ensure that the sheet hide preferences were set as planned.

Full VBA Code

Screen Captures

Screen capture of solution.
Screen capture of solution.

 

2016-10-30-11
The VBA Editor now requests a password when attempting to access modules.

Solution Document: 2016-10-30-securing-layout-and-formulas-solution (PWs: 123456)

Other Notes

  • Hiding and password protecting worksheets is not a viable option for preventing access to confidential data. Spreadsheet passwords are very easy to break.
  • Password protecting the VBA Editor is a good practice. If uses encounter any debug errors, they will not be brought to the code. Keep in mind, however, that you may want to give users the ability to fix code if it breaks.

Issues/Bugs

  • Emphasis from the Other Notes. Spreadsheet password are very easy to break, so make sure you do not include any confidential information on sheets, even if they are hidden and password protected.
  • If users copy and paste contents into any unlocked cell, the formatting of the cell will also paste, even if the protection settings prevent uses from modifying cell formats.

Related Exercises

Leave a Reply