I'm currently working on a new template for an employee shift schedule.

This workbook contains one sheet with the shifts schedule and 60 other sheets to help the planner to take into account the peaks of our activity. These sheets contain confidential data.

The users are the following:

- Every employee needs to be able to view the first sheet. They should not have access to view the other sheets.

- The planner needs to view all the sheets. This person makes many modifications every day. The modifications are made in the first sheet.

I thought I would use a workbook protection and use a password to protect the structure of the workbook. But since the planner needs to view all the sheets he would have to hide all of them before saving the file.

This is why I recorded two macros:

- Everytime the planner makes a change he runs a macro that hides all but the first sheet, protects the workbook's structure and saves the file.
- After that, he runs a macro that unprotects the workbook's structure and unhides all the sheets. He can then continue using the file.

Advantages:

- Everytime the workbook is saved only one sheet is visible to the other users. They can't easily see that there are hidden sheets because the unhide option is not available.

Disadvantages:

- The planner needs to break a habit and change the way he saves the file. The planner can live with that. I will put 2 new buttons on his quick access toolbar and assign them the 2 macros.

- If the other users are determined to find the hidden sheets they can still unhide them if they unprotect the workbook. This bothers us.

I tried to record a macro that would protect the workbook's structure with a password but Excel doesn't retain the password in the VBA code. So the structure is protected but not with a password.

I feel like I'm overthinking this. Is there a better way?