VBA to Hide All Sheets Except Certain Ones from all users except certain users

[slivesay]

Is there a way to hide all sheets w vba except 2 of the sheets to all users except two. The two users would need to see all sheets, because we are the ones that will be doing any updating - - - all other users will just be viewing the two non-hidden sheets.

 

[slivesay]

Here is how to incorporate the method I suggested.

First, create a new sheet named "AdminList".
Then, down column A, enter all the UserNames that you wish to allow to see ALL sheets.

Then, go into the VB Editor of this workbook, and place all of the code in the "ThisWorkbook" module.
It is absolutely essential that you put this code in this "ThisWorkbook" module! Do NOT create a new module and put it there. It will not work automatically unless placed in the ThisWorkbook" module!

Private Sub Workbook_Open()

    Dim wsSheet As Worksheet
    Dim adSheet As Worksheet
    Dim rng As Range
    Dim usr As String

'   Set admin sheet name
    Set adSheet = Sheets("AdminList")
  
'   Get user
    usr = Application.UserName

'   Unhide admin list sheet
    adSheet.Visible = True

'   Set range where list of people allowed to see all sheets exists
    Set rng = adSheet.Range("A:A")

'   If user on list, unhide all sheets except for lookup list
    If Application.WorksheetFunction.CountIf(rng, usr) > 0 Then
        For Each wsSheet In Worksheets
            wsSheet.Visible = True
        Next wsSheet
    End If
  
'   Rehide admin sheet
    adSheet.Visible = xlSheetVeryHidden

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wsSheet As Worksheet

'   Upon closing, hide all sheets except for two specified ones
    For Each wsSheet In Worksheets
        If (wsSheet.Name <> "Table of Contents (MAIN)") And (wsSheet.Name <> "Components (MAIN)") Then
            wsSheet.Visible = xlSheetVeryHidden
        End If
    Next wsSheet
  
'   Save changes
    ActiveWorkbook.Save
  
End Sub


Private Sub UnhideAdminSheet()
'   Run this manually when you want to unhide the AdminList sheet for editing
    Sheets("AdminList").Visible = True
End Sub

Now when you open the workbook, it will see if your username is in column A of the "AdminList" sheet, and if it is, it will show you all sheets (except the "AdminList" sheet).
If you are not on the list, it will only show you those two sheets.
Then, when you close the workbook, it will hide all but those two sheets.
So, if someone were to open the workbook while disabling macros/VBA, they will only see those two sheets.

If you need to edit people in the "AdminList" simply go to the VBA code here, and manually select and run the last procedure (the one named "UnhideAdminSheet").

This worked perfectly!!! I had to green' out all the other code I had placed there, but it 100% works. Do you think it would be hard to add in the code for logging all user and time/date when the book is opened on a sheets called 'user log' and to add in code to make the ctrl save, and the button save not work? I had it pop up a msg box and it wouldn't save.

 

[Joe4]

Do you think it would be hard to add in the code for logging all user and time/date when the book is opened on a sheets called 'user log' and to add in code to make the ctrl save, and the button save not work? I had it pop up a msg box and it wouldn't save.

Its possible, but that is a whole different question, so I would recommend opening up a new thread on that.
Be sure to mention that you already have code in the Workbook_Open event, as people will probably want to use that same event procedure to log those details.

 

[slivesay]

Its possible, but that is a whole different question, so I would recommend opening up a new thread on that.
Be sure to mention that you already have code in the Workbook_Open event, as people will probably want to use that same event procedure to log those details.

Okay, thank you so much! Thank you everyone for all the time and assistance you've provided me. I truly appreciate it! I hope everyone has a lovely Friday and weekend! ?

 

[slivesay]

Here is how to incorporate the method I suggested.

First, create a new sheet named "AdminList".
Then, down column A, enter all the UserNames that you wish to allow to see ALL sheets.

Then, go into the VB Editor of this workbook, and place all of the code in the "ThisWorkbook" module.
It is absolutely essential that you put this code in this "ThisWorkbook" module! Do NOT create a new module and put it there. It will not work automatically unless placed in the ThisWorkbook" module!

Private Sub Workbook_Open()

    Dim wsSheet As Worksheet
    Dim adSheet As Worksheet
    Dim rng As Range
    Dim usr As String

'   Set admin sheet name
    Set adSheet = Sheets("AdminList")

'   Get user
    usr = Application.UserName

'   Unhide admin list sheet
    adSheet.Visible = True

'   Set range where list of people allowed to see all sheets exists
    Set rng = adSheet.Range("A:A")

'   If user on list, unhide all sheets except for lookup list
    If Application.WorksheetFunction.CountIf(rng, usr) > 0 Then
        For Each wsSheet In Worksheets
            wsSheet.Visible = True
        Next wsSheet
    End If

'   Rehide admin sheet
    adSheet.Visible = xlSheetVeryHidden

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wsSheet As Worksheet

'   Upon closing, hide all sheets except for two specified ones
    For Each wsSheet In Worksheets
        If (wsSheet.Name <> "Table of Contents (MAIN)") And (wsSheet.Name <> "Components (MAIN)") Then
            wsSheet.Visible = xlSheetVeryHidden
        End If
    Next wsSheet

'   Save changes
    ActiveWorkbook.Save

End Sub


Private Sub UnhideAdminSheet()
'   Run this manually when you want to unhide the AdminList sheet for editing
    Sheets("AdminList").Visible = True
End Sub

Now when you open the workbook, it will see if your username is in column A of the "AdminList" sheet, and if it is, it will show you all sheets (except the "AdminList" sheet).
If you are not on the list, it will only show you those two sheets.
Then, when you close the workbook, it will hide all but those two sheets.
So, if someone were to open the workbook while disabling macros/VBA, they will only see those two sheets.

If you need to edit people in the "AdminList" simply go to the VBA code here, and manually select and run the last procedure (the one named "UnhideAdminSheet")

Here is how to incorporate the method I suggested.

First, create a new sheet named "AdminList".
Then, down column A, enter all the UserNames that you wish to allow to see ALL sheets.

Then, go into the VB Editor of this workbook, and place all of the code in the "ThisWorkbook" module.
It is absolutely essential that you put this code in this "ThisWorkbook" module! Do NOT create a new module and put it there. It will not work automatically unless placed in the ThisWorkbook" module!

Private Sub Workbook_Open()

    Dim wsSheet As Worksheet
    Dim adSheet As Worksheet
    Dim rng As Range
    Dim usr As String

'   Set admin sheet name
    Set adSheet = Sheets("AdminList")
 
'   Get user
    usr = Application.UserName

'   Unhide admin list sheet
    adSheet.Visible = True

'   Set range where list of people allowed to see all sheets exists
    Set rng = adSheet.Range("A:A")

'   If user on list, unhide all sheets except for lookup list
    If Application.WorksheetFunction.CountIf(rng, usr) > 0 Then
        For Each wsSheet In Worksheets
            wsSheet.Visible = True
        Next wsSheet
    End If
 
'   Rehide admin sheet
    adSheet.Visible = xlSheetVeryHidden

End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wsSheet As Worksheet

'   Upon closing, hide all sheets except for two specified ones
    For Each wsSheet In Worksheets
        If (wsSheet.Name <> "Table of Contents (MAIN)") And (wsSheet.Name <> "Components (MAIN)") Then
            wsSheet.Visible = xlSheetVeryHidden
        End If
    Next wsSheet
 
'   Save changes
    ActiveWorkbook.Save
 
End Sub


Private Sub UnhideAdminSheet()
'   Run this manually when you want to unhide the AdminList sheet for editing
    Sheets("AdminList").Visible = True
End Sub

Now when you open the workbook, it will see if your username is in column A of the "AdminList" sheet, and if it is, it will show you all sheets (except the "AdminList" sheet).
If you are not on the list, it will only show you those two sheets.
Then, when you close the workbook, it will hide all but those two sheets.
So, if someone were to open the workbook while disabling macros/VBA, they will only see those two sheets.

If you need to edit people in the "AdminList" simply go to the VBA code here, and manually select and run the last procedure (the one named "UnhideAdminSheet").

One last question --- I wanted to see the the workbook from a user who isn't listed on the "AdminList", so I took my user name off. It works amazingly, but I cannot get all sheets to appear after I put my name back on the list. How do I get it to show all sheets for me again? Thank you so much for all your assistance and your time. You whipped this coding up in a flash and it is brilliant. I truly appreciate all your help.

 

[Joe4]

One last question --- I wanted to see the the workbook from a user who isn't listed on the "AdminList", so I took my user name off. It works amazingly, but I cannot get all sheets to appear after I put my name back on the list. How do I get it to show all sheets for me again? Thank you so much for all your assistance and your time. You whipped this coding up in a flash and it is brilliant. I truly appreciate all your help.

It should just be a matter of adding your name back to the "AdminList" sheet, saving, closing, and re-opening.