Shared Workbooks with Hidden Tabs

Hi,

I have a shared file with 30 tabs (each tab managed by a different user). I only want each user to see the data in his tab, but all of the other tabs hidden or blank so they cannot access or see the data in other tabs. Does anyone have any ideas on how I can achieve this? The reason is each sheet has sensitive payroll information.

Any thoughts appreciated.

Thank you,
Jack

It is impossible to hide sheets from anybody with a modicum of VBA skills.

This is the second best you can do, the best is to only distribute one sheet to one person. Be aware that if someone disables Macros, they can access the entire book.
You need a Splash sheet or a sheet that will not be deleted. If the Tab Names are not the same as the UserNames, you will need to cross reference Sheet Names to UserNames. See Function GetSheet.

Code:

---

Option Explicit

Private Sub Workbook_Open()
  DeleteSheets
End Sub

Sub DeleteSheets()
'Deletes most sheets in Book
Dim CurrentUser As String, UserSheet As String
Dim Sht As Worksheet

CurrentUser = Environ("UserName")

'List Authorized UserNames (Full Viewers) as necessary
    If CurrentUser = "JackChang" Then Exit Sub
    If CurrentUser = "TomJones" Then Exit Sub
    If CurrentUser = "JoeSmith" Then Exit Sub
   
    UserSheet = GetSheet(CurrentUser)
   
    Application.DisplayAlerts = False
      For Each Sht In Sheets
          If Sht.Name = "Splash" Or Sht.Name = UserSheet Then GoTo SheetNext
          Sht.Delete
SheetNext:
      Next
    Application.DisplayAlerts = True
End Sub

Private Function GetSheet(CurrentUser As String) As String
'Cross reference UserName to Sheet Name

    Select Case CurrentUser
          'UserName then SheetName
      Case Is = "JDoe": GetSheet = "Doe, J."
      Case Is = "SamT": GetSheet = "Sam Tyler"
      Case Is = "DisneyLand ": GetSheet = "Mickey Mouse"
      'Continue for all Users and sheet names
      Case Else: GetSheet = "Splash" 'All Sheets will be deleted if not Proper User
    End Select
End Function

---

Thanks for the info Sam! I thought it wasn't possible to hid all other sheets except one, but needed a confirmation since it was work related. Appreciate the help, hope you have a great day.