Multiple Password Control

Show and hide sheets based on different passwords

By Wyn Hopkins

Often we have spreadsheets where we’d like different people access to different sheets. One way of doing that is demonstrated in the linked Excel File. The approach uses a combination of an Excel Table with some VB Code. Having a table to control your passwords makes it much simpler to add new sheets or new users.

Purpose:
To allow multiple passwords to be set to control which sheets are hidden / visible.

The way it works:

The blue column headings of the table below are the passwords and then you select which sheets you want to display.
e.g. The password “123” (see table below) will show all sheets, while “pwd1” will only show the cover sheet.

Updates:

1. Simply add new password columns to the right by typing a new password next to the existing one.
2. Add new sheets to by typing the new sheet name on the next available row.

Note: It is strongly recommended to replace that hard coded name with a direct link to the sheet using the custom SheetName formula.

Further points to note:

• Alt + F11 to view the code.
• Uses Custom Sheetname function, to return dynamic sheet name. This prevents code from breaking if sheet names change.
• Workbook Open and Close events makes the Range “Password” and “tblPasswords” invisible, so that users can’t simply click on Name Manager to see the passwords.