Shared Workbooks with Hidden Tabs

[JackChang]

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

[SamT]

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.


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

[JackChang]

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.