+ Reply to Thread
Results 1 to 8 of 8

Show / Hide specific sheets per username

Hybrid View

  1. #1
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Show / Hide specific sheets per username

    Hi all,

    I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:

    Option Explicit
    
    Private Sub Workbook_Open()
        Dim UserList As Variant
        Dim SheetList As Variant
        Dim i As Integer, ShowSheet As Integer, j As Integer
        Dim MasterUser As String, CurrentUser As String
        
        UserList = Array("John", "Joe")
        SheetList = Array("US", "Combined")
        
        'this user can see all ** the sheets
        MasterUser = "Jane"
        
        'see who the user is
        CurrentUser = Environ("username")
        
        'set ShowSheet to -1 so no sheets are shown if the user isn't found
        ShowSheet = -1
        
        'find the index ** the correct sheet for this user
        For i = LBound(UserList) To UBound(UserList)
            If UCase(UserList(i)) = UCase(CurrentUser) Then
                ShowSheet = i
                Exit For
            End If
        Next i
        
        'hide all sheets except the one the user should see
        With ThisWorkbook
            For j = LBound(SheetList) To UBound(SheetList)
                If j = ShowSheet Or CurrentUser = UCase(MasterUser) Then
                    .Sheets(SheetList(j)).Visible = xlSheetVisible
                Else
                    .Sheets(SheetList(j)).Visible = xlSheetVeryHidden
                End If
            Next j
        End With
    End Sub
    Does anyone know why the above code does not respect the Master User "Jane" should be able to see all sheets?



    Original source for this code was found here:

    HTML Code: 
    Last edited by byte; 03-10-2014 at 06:13 PM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,879

    Re: Show / Hide specific sheets per username

    I have not checked the rest ** the code, but if it works with other users, just add the blue parts to show all sheets to Masteruser

    Private Sub Workbook_Open()
        Dim UserList As Variant
        Dim SheetList As Variant
        Dim i As Integer, ShowSheet As Integer, j As Integer
        Dim MasterUser As String, CurrentUser As String
        
        UserList = Array("John", "Joe")
        SheetList = Array("US", "Combined")
        
        'this user can see all ** the sheets
        MasterUser = "Jane"
        
        'see who the user is
        CurrentUser = Environ("username")
        
        if currentuser = masteruser then
          for i = 1 to sheets.count
            sheets(i).visible = xlSheetVisible
          next i
       else
    
        'set ShowSheet to -1 so no sheets are shown if the user isn't found
        ShowSheet = -1
        
        'find the index ** the correct sheet for this user
        For i = LBound(UserList) To UBound(UserList)
            If UCase(UserList(i)) = UCase(CurrentUser) Then
                ShowSheet = i
                Exit For
            End If
        Next i
        
        'hide all sheets except the one the user should see
        With ThisWorkbook
            For j = LBound(SheetList) To UBound(SheetList)
                If j = ShowSheet Or CurrentUser = UCase(MasterUser) Then
                    .Sheets(SheetList(j)).Visible = xlSheetVisible
                Else
                    .Sheets(SheetList(j)).Visible = xlSheetVeryHidden
                End If
            Next j
        End With
      end if
    End Sub
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Show / Hide specific sheets per username

    Hi Kaper,

    Thanks but this doesn't affect anything i.e. doesn't respect the master user chosen.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,879

    Re: Show / Hide specific sheets per username

    First question is about Jane ;-)
    Have you tried:
    sub test()
    msgbox ">" &  Environ("username") & "<"
    end sub
    Does it show exactly
    HTML Code: 
    if not - correct the code.
    May be Ucase or Trim is needed.

  5. #5
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Show / Hide specific sheets per username

    Hi Kaper,

    Thanks again for your reply. Yes, I have double, triple checked the username and it does show Jane, I have also tried with UCase...

        If UCase(CurrentUser) = UCase(MasterUser) Then
          For i = 1 To Sheets.Count
            Sheets(i).Visible = xlSheetVisible
          Next i
       Else
    But the code still does not show all sheets to master user

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,879

    Re: Show / Hide specific sheets per username

    Sorry to say so - the problem is not just with the code - it is probably elsewhere.
    You have shown no sample workbook so hard to judge.
    Try the attached and compare yourself the differences.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Show / Hide specific sheets per username

    Thanks Kaper, not sure how or why but I copied the code from your sample (sorry about the no sample on my side) and this worked in my workbook...again I am not so sure why but maybe I had a slight code error in my original :~

    Thanks again Kaper

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,879

    Re: Show / Hide specific sheets per username

    I am glad to hear that. It is good time to mark thread as solved.
    See http://www.excelforum.com/forum-rule...rum-rules.html how to do it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Username and Password Form - Show Username in Sheet
    By ryan180 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2013, 07:06 PM
  2. Restrict HIDE / UNHIDE sheets by Username
    By coreytroy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-24-2012, 11:47 PM
  3. Using Userform to Hide/Show specific Sheets in Work Book
    By Craigs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2011, 05:40 AM
  4. Use checkbox to show/hide sheets?
    By Zheno in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-03-2010, 02:49 PM
  5. Hide then show specific column
    By littleandyjai in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-14-2010, 07:42 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1