Gatekeeper
Gatekeeper for Excel is a Microsoft Excel® add-in (for Excel 2007 & 2010) that allows you to define worksheet protection rules that are applied automatically when the workbook is opened. The protection rules are based on your own units (workgroups of sorts), user groups, & users. These rules are then applied automatically by Gatekeeper when the workbook is loaded in Excel based on the logged in Gatekeeper user. Gatekeeper protection rules allow you to define which cells can be edited by who, worksheet visibility, restrict certain macro functionality, or request authorisation clearance from another user in order to perform certain macro actions.
As a workbook administrator Gatekeeper allows you to define your own units & user groups (unrelated to the Windows user domain) to mirror the workbook's user roles more closely. For example, different people in the same department might be responsible for updating different data sets from the same workbook. Gatekeeper will allow you to define a specific user group for each. Create the respective users, assign a user name and password, and attach them to the required user group. Once the visibility rules are defined for each group, and the users log in Gatekeeper and open the workbook, worksheets and/or cells will be automatically locked or hidden according to the rules set, thereby restricting accidental alteration/misuse of the data.
As a workbook administrator, through the Gatekeeper add-in you can:
- Add/Edit/Delete units for the users who will be using your workbook;
- Add/Edit/Delete user-groups for your users;
- Add/Edit/Delete user-accounts for the Gatekeeper add-in;
- Take advantage of an SQLite database for a hassle free creation of the central, serverless users database;
- Define units and user groups in line with the user's on the job responsibilities;
- Generate reports on user-accounts and their status;
- Manage protection rules, worksheet visibility, and protection options based on units, user-groups, access level, &/or users (or a combination thereof);
- Define 'fixed' worksheets that will always be visible to users (whether or not the Gatekeeper add-in is running or not);
- Define 'fixed' or 'floating' cell protection rules.
As a VBA programmer you can also*:
- Enable/disable macro functionality based on the workbook's user;
- Build authorisation requests (require another user to authorise an action) for parts of your macros to limit who can use what.
*Requires a reference to Gatekeeper in your VBA project.
As a non-administrative Gatekeeper user you can:
- Manage your own user password;
- Easily identify the worksheets and cells you are allowed to alter;
- Only run macros that you are allowed to use (if the necessary VBA precautions are put in place), therefore avoiding the risk of performing any unwanted actions on the workbook.
NOTE: Workbooks with Gatekeeper protection rules can still be opened and used by users who do not have the Gatekeeper add-in installed (the rules will not be enforced). The visible worksheets and unprotected cells therein will depend on how the rules are set within Gatekeeper. Users who do not have Gatekeeper installed will only be able to access those worksheets which are explicitly set within Gatekeeper to be accessible by any user.