Forum Discussion

cking1333's avatar
cking1333
Copper Contributor
May 16, 2024

Hide/Unhide Worksheets Based on Name & Password

Greetings, 

 

I found the code below and it gets me moving in the right direction but I would like to have significantly more autonomy over the users and which worksheets they can access within a spreadsheet. Here is the code: 

Private Sub Workbook_Open()
Dim pword As String
On Error GoTo endit
      pword = InputBox("Enter logon information to access permitted worksheets")
      Select Case pword
      Case Is = "TEST": Sheets("NOT MANAGER").Visible = True
      Case Is = "MANAGER": Call UnHideAllSheets
End Select
Sheets("Dummy").Visible = False
Exit Sub
endit:
MsgBox "Incorrect Password"
End Sub

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
       Sheets("Dummy").Visible = xlSheetVisible
           For Each sht In ActiveWorkbook.Sheets
           If sht.Name <> "Dummy" Then
      sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
End Sub

 

Sub UnHideAllSheets()
    Application.ScreenUpdating = False
    Dim n As Single
    For n = 1 To Sheets.Count
        Sheets(n).Visible = True
    Next n
    Application.ScreenUpdating = True
End Sub

 

 I like the idea of a dummy worksheet that the spreadsheet uses but I would like to incorporate a username in conjunction with a password to give access to users based on what they need access to. Additionally, rather than having to change usernames and passwords in VBA, I would like to have a hidden worksheet that an administrator can go in to add/delete/modify usernames, passwords and the worksheets each user has access to. 

 

Not sure if this is possible but if so it is definitely beyond me and I would appreciate any assistance. 

 

Thanks,

C

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    cking1333 

    To achieve the level of control and flexibility you're looking for, we can expand upon the existing VBA code. Here's a comprehensive solution that involves:

    1. Storing usernames, passwords, and sheet permissions in a hidden worksheet.
    2. Checking the login information against this hidden worksheet.
    3. Showing or hiding worksheets based on the permissions.

    Let's break this down step-by-step.

    Step 1: Setting Up the Hidden Worksheet

    Create a hidden worksheet (let's name it "UserAccess") with the following structure:

    Username

    Password

    Sheet1

    Sheet2

    ...

    user1

    pass1

    TRUE

    FALSE

    ...

    user2

    pass2

    FALSE

    TRUE

    ...

    Each column after the "Password" column corresponds to a worksheet name. Use TRUE or FALSE to indicate whether the user has access to that sheet.

    Step 2: VBA Code to Handle User Login and Sheet Access

    Here's the updated VBA code to handle login and sheet access based on the "UserAccess" worksheet:

    Vba Code is untested, please backup your file.

     

    Private Sub Workbook_Open()
        Dim username As String
        Dim pword As String
        Dim ws As Worksheet
        Dim wsAccess As Worksheet
        Dim lastRow As Long
        Dim userFound As Boolean
        Dim sheetCol As Integer
        Dim cell As Range
        
        On Error GoTo endit
    
        ' Get login information
        username = InputBox("Enter your username")
        pword = InputBox("Enter your password")
    
        ' Set the UserAccess worksheet
        Set wsAccess = ThisWorkbook.Sheets("UserAccess")
        wsAccess.Visible = xlSheetVisible ' Temporarily make it visible
    
        ' Find the last row in the UserAccess worksheet
        lastRow = wsAccess.Cells(wsAccess.Rows.Count, 1).End(xlUp).Row
        
        userFound = False
        For Each cell In wsAccess.Range("A2:A" & lastRow) ' Assuming headers are in row 1
            If cell.Value = username And cell.Offset(0, 1).Value = pword Then
                userFound = True
                ' Loop through sheets and set visibility based on UserAccess
                For Each ws In ThisWorkbook.Sheets
                    If ws.Name <> "UserAccess" And ws.Name <> "Dummy" Then
                        sheetCol = Application.WorksheetFunction.Match(ws.Name, wsAccess.Rows(1), 0)
                        If cell.Offset(0, sheetCol - 1).Value = True Then
                            ws.Visible = xlSheetVisible
                        Else
                            ws.Visible = xlSheetVeryHidden
                        End If
                    End If
                Next ws
                Exit For
            End If
        Next cell
    
        wsAccess.Visible = xlSheetVeryHidden ' Hide the UserAccess sheet again
    
        If Not userFound Then
            MsgBox "Incorrect Username or Password"
            End
        End If
    
        Sheets("Dummy").Visible = False
        Exit Sub
    
    endit:
        MsgBox "Error occurred during login process"
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim sht As Worksheet
        Application.ScreenUpdating = False
        Sheets("Dummy").Visible = xlSheetVisible
        For Each sht In ActiveWorkbook.Sheets
            If sht.Name <> "Dummy" And sht.Name <> "UserAccess" Then
                sht.Visible = xlSheetVeryHidden
            End If
        Next sht
        Application.ScreenUpdating = True
    End Sub

    Step 3: Testing the Setup

    1. Create the "UserAccess" worksheet with the specified structure and fill in some test data.
    2. Create a "Dummy" sheet.
    3. Add the VBA code to the workbook (press Alt + F11 to open the VBA editor, then add the code to the ThisWorkbook object).
    4. Save and close the workbook.
    5. Reopen the workbook to test the login and sheet access functionality.

    Additional Notes:

    • Ensure the "UserAccess" sheet is hidden by default.
    • The password protection is simple and not encrypted. For stronger security, consider using a more advanced method or an external library.

    This setup should provide the flexibility to manage users and their access to different worksheets without modifying the VBA code every time a change is needed. The text, step and code was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

    • cking1333's avatar
      cking1333
      Copper Contributor
      WOW! That's amazing...thank you so much! There are a couple issues when a user will login though:
      1) both the Dummy ws & the Username ws do not hide when a user logs in
      2) The "Error occurred during login process" message box always pops up, even if the login information is correct

      Can these issue be resolved?
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        cking1333 

        Let's address the issues you mentioned:

        1. Hiding the Dummy worksheet and the Username worksheet upon login: We'll ensure that these sheets are hidden after a successful login.
        2. Preventing the "Error occurred during login process" message box from appearing when login information is correct: We'll refine the error handling and logic to correctly differentiate between a successful and unsuccessful login attempt.

        Here's the revised VBA code:

        Vba Code is untested, please backup your file.

         

        Private Sub Workbook_Open()
            Dim username As String
            Dim pword As String
            Dim ws As Worksheet
            Dim wsAccess As Worksheet
            Dim lastRow As Long
            Dim userFound As Boolean
            Dim sheetCol As Integer
            Dim cell As Range
            
            On Error GoTo endit
        
            ' Get login information
            username = InputBox("Enter your username")
            pword = InputBox("Enter your password")
        
            ' Set the UserAccess worksheet
            Set wsAccess = ThisWorkbook.Sheets("UserAccess")
            wsAccess.Visible = xlSheetVisible ' Temporarily make it visible
        
            ' Find the last row in the UserAccess worksheet
            lastRow = wsAccess.Cells(wsAccess.Rows.Count, 1).End(xlUp).Row
            
            userFound = False
            For Each cell In wsAccess.Range("A2:A" & lastRow) ' Assuming headers are in row 1
                If cell.Value = username And cell.Offset(0, 1).Value = pword Then
                    userFound = True
                    ' Loop through sheets and set visibility based on UserAccess
                    For Each ws In ThisWorkbook.Sheets
                        If ws.Name <> "UserAccess" And ws.Name <> "Dummy" Then
                            sheetCol = Application.WorksheetFunction.Match(ws.Name, wsAccess.Rows(1), 0)
                            If cell.Offset(0, sheetCol - 1).Value = True Then
                                ws.Visible = xlSheetVisible
                            Else
                                ws.Visible = xlSheetVeryHidden
                            End If
                        End If
                    Next ws
                    Exit For
                End If
            Next cell
        
            If userFound Then
                wsAccess.Visible = xlSheetVeryHidden ' Hide the UserAccess sheet again
                Sheets("Dummy").Visible = xlSheetVeryHidden ' Hide the Dummy sheet
            Else
                MsgBox "Incorrect Username or Password"
                End
            End If
        
            Exit Sub
        
        endit:
            MsgBox "Error occurred during login process"
        End Sub
        
        Private Sub Workbook_BeforeClose(Cancel As Boolean)
            Dim sht As Worksheet
            Application.ScreenUpdating = False
            Sheets("Dummy").Visible = xlSheetVisible
            Sheets("UserAccess").Visible = xlSheetVisible ' Ensure UserAccess is visible for admin purposes
            For Each sht In ActiveWorkbook.Sheets
                If sht.Name <> "Dummy" And sht.Name <> "UserAccess" Then
                    sht.Visible = xlSheetVeryHidden
                End If
            Next sht
            Application.ScreenUpdating = True
        End Sub

        Explanation of Changes:

        1. Conditional Check for Successful Login: The userFound flag is used to determine if the login was successful. The If userFound Then block ensures the hidden worksheets are properly hidden after a successful login.
        2. Hiding Sheets: Both "UserAccess" and "Dummy" sheets are hidden if the login is successful.
        3. Error Handling: The error message will only display if an actual error occurs during the login process, and not when the login is simply incorrect.

        Testing the Changes:

        1. Update the "UserAccess" worksheet with your user data.
        2. Ensure the "UserAccess" sheet is hidden by default.
        3. Add the revised VBA code to the ThisWorkbook object in the VBA editor.
        4. Save, close, and reopen the workbook to test the updated login and access control functionality.

        These changes should resolve the issues you encountered and provide the desired functionality for hiding/unhiding worksheets based on username and password. You can also develop the rest yourself (if more is needed) :smile:.

Resources