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.

 

[VBE313]

Paste this code inoto the "ThisWorkbook" Microsoft Excel Object. Get the Usernames of the users that you want to hide the sheets for.

Sub Workbook_Open()
Dim ws As Worksheet, ws_count As Long, i As Long
Select Case Application.UserName
    Case "Insert Users Application Name Here", "silvesay"
        ws_count = ActiveWorkbook.Sheets.Count
        For i = 1 To ws_count
            If Sheets(i).Name = "sheet1" Or Sheets(i).Name = "Sheet2" Then
            Else
                Sheets(i).Visible = xlSheetHidden
            End If
        Next i
    Case Else
End Select
End Sub

 

[slivesay]

Thank you so much!

Question:
Is there anyway to state the two users that can see all sheets instead of who can only see two sheets? The users who only see two sheets are the whole office and other offices in other states.

 

[VBE313]

Sub Workbook_Open()
Dim ws As Worksheet, ws_count As Long, i As Long
ws_count = ActiveWorkbook.Sheets.Count
Select Case Application.UserName
    Case "Insert Users Application Name Here", "silvesay"
        For i = 1 To ws_count
            Sheets(i).Visible = xlVisible
        Next i
    Case Else
        For i = 1 To ws_count
            If Sheets(i).Name = "sheet1" Or Sheets(i).Name = "Sheet2" Then
            Else
                Sheets(i).Visible = xlSheetHidden
            End If
        Next i
End Select
End Sub

 

[Joe4]

Note that there is a potential issue with taking this approach. If they disable VBA when they open the file, the VBA code will never run.

An approach I have seen people use is to go about it the reverse way. By default, save the file with all the sheets in question hidden.
Then, have the VBA code verify who they are, and unhide the sheets, if appropriate.
And then have a BeforeSave event that re-hides all those particular sheet upon saving.

That way, if they disable VBA, when they open the file, those sheets in question will never be visible.

 

[slivesay]

Note that there is a potential issue with taking this approach. If they disable VBA when they open the file, the VBA code will never run.

An approach I have seen people use is to go about it the reverse way. By default, save the file with all the sheets in question hidden.
Then, have the VBA code verify who they are, and unhide the sheets, if appropriate.
And then have a BeforeSave event that re-hides all those particular sheet upon saving.

That way, if they disable VBA, when they open the file, those sheets in question will never be visible.

How would I be able to keep up w all the users that need access across several states? It is a lot of people.

 

[Joe4]

How would I be able to keep up w all the users that need access across several states? It is a lot of people.

One way would be to keep a running list in a hidden sheet in the file.

However, if security is an important thing here, you may want to consider using something other than Excel.
Excel's security is not very high tech, and can be defeated without too much effort.

 

[slivesay]

One way would be to keep a running list in a hidden sheet in the file.

However, if security is an important thing here, you may want to consider using something other than Excel.
Excel's security is not very high tech, and can be defeated without too much effort.

I'm not concerned w people trying to disable the vba nor does the security need to be really high. The spreadsheet is already created and needs to be updated by two ppl of the company only and the rest of the company users need to be able to see two sheets only. It is an informational spreadsheet not something that requires high level of security.

 

[slivesay]

This is what I had, but it all over the place not working other than hiding the sheets.

Sub HideCertainSheets()

Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
wsSheet.Visible = xlSheetVisible
If Application.UserName = "susan.livesay" Then
wsSheet.Visible = True

Else

wsSheet.Visible = xlSheetVeryHidden
If wsSheet.Name = "Table of Contents (MAIN)" Or wsSheet.Name = "Components (MAIN)" Then
wsSheet.Visible = True
End If
End If


Next wsSheet
End Sub

 

[Joe4]

I'm not concerned w people trying to disable the vba nor does the security need to be really high. The spreadsheet is already created and needs to be updated by two ppl of the company only and the rest of the company users need to be able to see two sheets only. It is an informational spreadsheet not something that requires high level of security.

If that is the case, then why be concerned about it at all then?

I am just a little confused, as you seem to be concerned about it enough to go to the effort of trying to hide it from most members. But then you don't seem too concerned if they disable VBA, bypassing the code, and seeing everything anyway.

By the way, in a lot of systems, VBA may be disabled by default. So it isn't the fact that they need to know what they are doing in order to disable VBA.
As matter as fact, more often than not, they need to know what they are doing in order to enable security (depending on the security settings).