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]

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).

The other sheets are other information my boss wants in there and for my boss to only see (the other sheets). The use of the two sheets I want visible are for information needed for orders (part information).

 

[slivesay]

The other sheets are other information my boss wants in there and for my boss to only see (the other sheets). The use of the two sheets I want visible are for information needed for orders (part information).

 

[GWteB]

While reading along, the following question arises: will the Excel workbook in question be distributed (e.g. via email) or is it a single copy on a shared network location accessible to all employees involved? In case of the first situation a macro that hides the desired worksheets (xlVeryHidden) and saves the workbook as a "duplicate for distribution only" might work.

 

[slivesay]

While reading along, the following question arises: will the Excel workbook in question be distributed (e.g. via email) or is it a single copy on a shared network location accessible to all employees involved? In case of the first situation a macro that hides the desired worksheets (xlVeryHidden) and saves the workbook as a "duplicate for distribution only" might work.

It is on a shared network. I work from a hidden folder to update it, then replace the file on the network for everyone when theres an update.

 

[slivesay]

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

I'm getting an error in the last Sheets(i).Visible = xlSheetHidden section.

 

[GWteB]

Something like that, since you said ...

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.

 

[GWteB]

It is on a shared network. I work from a hidden folder to update it, then replace the file on the network for everyone when theres an update.

In that case you might consider to create a "maintenance" worksheet with a button on it. Assign a macro to the button that does the following steps:
- save the workbook within that hidden folder with all sheets visible for you and your co-worker;
- hide the desired sheets;
- save workbook as duplicate on the network location for everyone else, forcing to overwrite an existing one, regardless someone has it open or note (so as read-only).
- open original (updated) workbook;
- close the duplicate.
In that way you (or your co-worker) are able to update the "view" version anytime you like.

EDIT:
... and you do not depend on event procedures, so no problem if anyone else has disabled VBA.

 

[slivesay]

In that case you might consider to create a "maintenance" worksheet with a button on it. Assign a macro to the button that does the following steps:
- save the workbook within that hidden folder with all sheets visisble for you and your co-worker;
- hide the desired sheets;
- save workbook as duplicate on the network location for everyone else, forcing to overwrite an existing one, regardless someone has it open or note (so as read-only).
- open original (updated) workbook;
- close the duplicate.
In that way you (or your co-worker) are able to update the "view" version anytime you like.

would this allow for hyperlinks, a search box and all of the other existing vba coding to still work properly?

 

[Joe4]

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").

 

[GWteB]

I often use such an approach and it does not affect the operation of my worksheets and underlying VBA code.