encrypt a tab to prevent view or opening

[NoelD]

Is there a way to protect a tab in a workbook from being visible to some shared users

 

[offthelip]

you make the sheet "veryhidden" which can only be done ( and undone) using vba and if you then password protect the vba project that is going to stop all but the very determined
code likethis:

Sheets("Sheet1").Visible = xlVeryHidden

 

[Candyman8019]

Taking it one step further from offthelip's post, I use a couple of different methods depending on the need. As offthelip stated, use the xlveryhidden attribute in a workbook_open script. Then on one of my pages, I designate a cell for a "password". When the user enters that password, it will unhide the sheet(s) accordingly.

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Sheet11.Range("AuditCode")) Is Nothing Then
If Sheet11.Range("AuditCode").Value = "Showmethemoney" Then
Application.EnableEvents = false
' Display Audit Sheet
  Sheet12.Visible = xlSheetVisible
  Sheet11.Range("AuditCode").Value = ""
  Sheet12.Activate
End If
End If
Application.EnableEvents = True
End Sub

Alternatively, if you know the user ID's of the people using the sheet, you could use something like the following in a workbook_open script:

Private Sub workbook_open()
Dim whodis As String
whodis = Environ("UserName")
If whodis = "ThisID" Or whodis = "ThatID" Then
   Sheet11.Visible = xlSheetVisible

End If
End Sub

 

[NoelD]

Thanks for the information, but as i am new to VBA...I cant follow the code.

I want to have a password on one sheet that will open a hidden sheet in a workbook containing 5 sheets

 

[Candyman8019]

To get this code working, choose a cell where you want the user to enter the password. For mine, I gave it a range name called AuditCode which you'll also see in the vba referenced as range("AuditCode").value:

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Sheet11.Range("AuditCode")) Is Nothing Then
If Sheet11.Range("AuditCode").Value = "Showmethemoney" Then
Application.EnableEvents = false
' Display Audit Sheet
  Sheet12.Visible = xlSheetVisible
  Sheet11.Range("AuditCode").Value = ""
  Sheet12.Activate
End If
End If
Application.EnableEvents = True
End Sub

This code is to be placed in the sheet where the user would enter the password. In my case it is a 'Defects' sheet:

When the user enters the correct password in that field, this will:
Unhide sheet12, then clear out the password field and navigate to sheet12.

 

[Candyman8019]

You could unhide multiple sheets by adding applicable lines such as:

sheet12.visible = xlsheetVisible
sheet8.visible = xlsheetVisible
sheet9.visible = xlsheetVisible

etc.