Hide/Unhide Worksheets Based on Name & Password

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:

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.