Windows Environ restricting access

[jaypatel]

Hi

I have searched high and low for this solution...

If I have 4 sheets in a workbook, named Summary, Anne, Jay, Simon.

I am looking for Anne to view only her sheet, Simon to view only his sheet, and Jay to view all....
(Their names could be in cell A1 of each of their sheets).
How would I do this with "Environ" property?

Thanks

Jay

 

[Peter_SSs]

Assuming
- Anne & Simon are only allowed to see their own sheet and not the Summary
- Everybody's sheet name is the same as their environ user name, apart from perhaps capitalisation

then try this with a copy of your workbook.

1. Add a new sheet that everybody is allowed to see. Could be blank. I've named that sheet 'All'. Adjust code if you want to use a different name.
2. Add the following two codes to the ThisWorkbook module in the vba window.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ws As Worksheet
    
    For Each ws In Worksheets
      If ws.Name <> "All" Then ws.Visible = xlSheetVeryHidden
    Next ws
    ThisWorkbook.Save
End Sub

Private Sub Workbook_Open()
    Dim user As String
    Dim ws As Worksheet
    
    user = LCase(Environ("username"))
    Select Case user
      Case "jay"
        For Each ws In Worksheets
          ws.Visible = True
        Next ws
      Case "anne", "simon"
        Sheets(user).Visible = True
      Case Else
        'Unhide nothing
    End Select
End Sub