How to Block other users from viewing Excel sheets

[Krishnab4u]

Dear All,
I used Environ "User Name" method in my workbook to assign Visibility of sheets with respect to the users.
Now, I dont want any of the users other than in the list to see all the sheets except one or two.
Please guide how to do the same.

Thank You.

Regards
Krishnamoorthy S

 

[mole999]

if the logged on user is <> to an environ then that should do it, you need to make sure that when closed all potential sheets are xlveryhidden, then you control who can see as opened and the lack of macro will not let them bypass the hidden code, you will need to password your module as well

 

[Krishnab4u]

Thank you, but how do I write code to check if the user is not in my list. Please guide.

 

[lrobbo314]

Something like this...

Private Sub Workbook_Open()
Dim Dict    As Object: Set Dict = CreateObject("Scripting.Dictionary")
Dim CurUser As String: CurUser = Environ("UserName")
Dim wsArr() As Variant: wsArr = Array("Sheet2", "Sheet3") 'Sheets that need to be hidden or unhidden


Dict.Add "User1", 1
Dict.Add "User2", 1
Dict.Add "User3", 1
Dict.Add "User4", 1 'Add all of the users who should be able to see the sheets


For i = 0 To UBound(wsArr)
    If Dict.exists(CurUser) Then
        Sheets(wsArr(i)).Visible = xlSheetVisible
    Else
        Sheets(wsArr(i)).Visible = xlSheetVeryHidden
    End If
Next i


End Sub

 

[mole999]

On that last I force either to upper or lower case the ENVIRON string, that way we don't worry about exact match issues.

 

[Krishnab4u]

On that last I force either to upper or lower case the ENVIRON string, that way we don't worry about exact match issues.

Thank you so much.