Hide/Unhide Worksheets Based on Name & Password

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) .