Restrict HIDE / UNHIDE sheets by Username

**coreytroy** · 09-24-2012, 07:53 PM

Is there a way to exit a sub to prevent a user UNHIDING(or HIDING) a sheet unless their username is a set name as below, but triggered by the user attempting to Unhide the sheet (right clicking on a Tab>Selecting a Sheet to Unhide)?

Private Sub Worksheet_Deactivate()
' Keep Sheet8 Hiden unless approved to view
If Application.UserName = "me" Or Application.UserName = "user2" Then
Sheet8.Visible = True
Else
Sheet8.Visible = False
MsgBox "YOU are not permitted to view sheet8 !", vbExclamation, "Warning !!!"
Exit Sub
End If
End Sub

**HaHoBe** · 09-24-2012, 11:47 PM

Hi, coreytroy,

you may use xlSheetVeryHidden which is only available from the VBE, and I won´t rely on Application.Username but take the name of the system instead:

Private Sub Workbook_Open()
Select Case LCase(Environ("username"))
  Case "yyz", "hahobe"
    Sheet8.Visible = True
  Case Else
    Sheet8.Visible = xlSheetVeryHidden
End Select
End Sub

And I try to avoid the Worksheet_Deactivate-Event and use the Worksheet_Activate instead:

Private Sub Worksheet_Activate()
' Keep Sheet8 Hiden unless approved to view
If Not Application.UserName = "me" Or Not Application.UserName = "user2" Then
  Sheet8.Visible = False
  MsgBox "YOU are not permitted to view sheet8 !", vbExclamation, "Warning !!!"
  Exit Sub
End If
End Sub

Ciao,
Holger