Consulting

Results 1 to 3 of 3

Thread: Shared Workbooks with Hidden Tabs

  1. #1

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •