Forum Discussion

Dboulton2001's avatar
Dboulton2001
Copper Contributor
Feb 16, 2024

Restricting access to specific worksheets in Excel.

HI,

I have a spreadsheet which is completed by a third party.  The information they add is about student progress.

We have 7 departments in our University. If each department has one worksheet in the larger spreadsheet, how could I prevent them from seeing the information on the other worksheets when they access it to check their students progress?  I want to restrict it so when they access the spreadsheet they only see theirs.

 

Thanks,

Dave

excel
Excel for web
  • MAngosto's avatar
    MAngosto
    Iron Contributor
    Feb 16, 2024

    Dboulton2001 

     

    You can use VBA code to run this. You can make sheets "VeryHidden", which would unable users to unhide any of these sheets. The drawback: you will have to change the code each time you send the document to each department. However, you could just write 7 different codes and assign them to a button just to select which sheets are VeryHidden or not, and then send the document accordingly.

     

    For setting the basic VeryHidden property to a specific sheet:

    Sub VeryHiddenSheet()

    ThisWorkbook.Sheets("NameOfDesiredHiddenSheet").Visible = xlSheetVeryHidden

End Sub

     

    If you want to make that sheet visible again you can do it with:

    Sub UnhideVeryHiddenSheet()

    ThisWorkbook.Sheets("NameOfDesiredUnhiddenSheet").Visible = xlSheetVisible

End Sub

     

    If by any chance you do not remember the names of the VeryHidden sheets and cannot find them, you can unhide any VeryHidden sheet that is in the document:

     

    Sub UnhideAnyVeryHiddenSheet()
  Dim wks As Worksheet

  For Each wks In Worksheets
      If wks.Visible = xlSheetVeryHidden Then 
         wks.Visible = xlSheetVisible
      End if
  Next wks
End Sub

     

    I would also recommend setting up a password to prevent users from viewing the code and running any macro. To do that, go to the developer tab > visual basic > right-click on the project > properties > protection > insert the password and click on "Lock project for viewing".

     

    Hope it satisfies your need.

     

    Martin

Resources