Workbook protection - prevent certain users to view some of the sheets

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?

Originally Posted by yudlugar

The worksheets have three options for the visible property:
-visible
-hidden
-veryhidden
Try setting the sheet to very hidden

Sheets(1).visible = xlveryhidden

This stops users unprotecting the workbook to get at the hidden sheets, you need to do it from within the vba editor. You can then put a password on the vba project to stop them doing this. It is not 100% secure but I expect it would be good enough for your purposes.

With regards to saving, my suggestion would be to use events to trigger the macros. Then the planner should be able to proceed as normal and the hiding/unhiding of the sheets should be done automatically.

You can use the beforesave event to run the macro you have written, with a slight change of a cancel added for the excel save. Then your planner will just need to press save (or save as) to trigger the macro. After it has saved, I would then suggest that you test the username and if it is that of the planner, you can then unhide the sheets.

The code would be something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim fname
For count = 2 To 60
    Sheets(count).Visible = xlVeryHidden
Next
Application.ScreenUpdating = False
fname = Application.GetSaveAsFilename
Cancel = True
ActiveWorkbook.SaveAs Filename:=fname
If Application.UserName = "Planner" Then
    For count = 2 To 60
        Sheet(count).Visible = xlVisible
    Next
End If
Application.ScreenUpdating = True
End Sub

and it needs to go in the "ThisWorkbook" object in the vbaproject

Edit: you would also want the last part of the code in the open event for the workbook:

Private Sub Workbook_Open()
application.screenupdating = false
If Application.UserName = "Planner" Then
    For count = 2 To 60
        Sheet(count).Visible = xlVisible
    Next
End If
application.screenupdating = true

Again, put this in the ThisWorkbook object

Sorry I took so much time to reply. Your suggestion is way above my level but I was able to use parts of it to resolve my problem. I made the beforesave macro to make the sheets veryhidden before saving. Then I wrote a different macro to unhide all the sheets - this macro tests the username first (anyway, I'm impressed that it's even possible to check the username before running a macro. That's so cool.) I was not able to link the 2 macros in one but that's fine. I put a button on the quick access toolbar of the planner. I made also a different macro that only unhides several sheets and this macro also tests usernames before running. So finally I have several different levels of visibility.

In the meantime I also realized that there is actually a way to put a password to protect workbook in the macro. It doesn't work while recording but it can be written in the code. So finally there were 2 possible solutions but I liked your suggestion with the veryhidden status better than password protecting the workbook.

Hi
You dont need code or Macros. I would suggest that you create a separate file for all the sheets the planner needs to update and link the data in the Summary Sheet to the Planning Sheets. Any updates in the planning sheets would then automatically update the Summary Sheet workbook even if it is not open.
Hope this helps.
Good luck.
Tony

How exactly would I do that?

This was actually my first thought but if I put a simple link then it doesn't reflect well all the changes. For exemple, if I add or delete row. I also need the comments to be visible as well as formatting.

Hi
You can either copy and paste the whole worksheet or paste individual cells. Follow these step:
1. Select all of the cells you want to link and then choose copy.
2. Move to the new Summary workbook and right click in the starting cell and choose "Paste Special".
Your cells will now contain a formula that links to the raw data. When the raw data changes the linked cells will change to reflect those changes.
Good luck.
Tony

Thank you for this suggestions but it didn't work out. It doesn't reflect well all the changes. For exemple, if I add or delete row. I also need the comments to be visible as well as formatting.

In the meantime I organized the file differently so I don't need a separate workbook anymore.

Thank you so much for your replies. Your suggestions seem way above my level so I will need some time. Thank you!