Run time error 1004 in VBA Code - the password you supplied is not correct

[born2run]

Hi ,

I have spent significant amount of time troubleshooting this error with little success. scenario is if user enters valid user ID and password in the user form and then he/she should see only the authorized tab. even if the user ID and passwords are valid , the excel raises run time error 1004 "the password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization. I can send the workbook as well if it needed . thanks for the help.

if I debug the code , it points to code

Sheets(sSName).Unprotect Password:=txtPass.Text

I am not sure if it helps but this error is not consistent - sometimes it shows up and other times it works fine. even if I close the error message, it still shows the correct tab for the user.

the code is -

Dim bOK2Use As Boolean


Private Sub btnOK_Click()
    Dim bError As Boolean
    Dim sSName As String
    Dim sPass  As String
    Dim p As DocumentProperty
    Dim bSetIt As Boolean


    bOK2Use = False
    bError = True
 
    If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
        bError = False
        Select Case txtUser.Text
            Case "Adam Shea"
                sSName = "Loblaw"
                If txtPass.Text <> "Adam123" Then bError = True
            Case "Brianne Bell"
                sSName = "Walmart"
                If txtPass.Text <> "weston123" Then bError = True
            Case Else
                bError = True
        End Select
    End If
    If bError Then
        MsgBox "Invalid User Name or Password"
    Else
        'Set document property
        bSetIt = False
        For Each p In ActiveWorkbook.CustomDocumentProperties
            If p.Name = "auth" Then
                p.Value = sSName
                bSetIt = True
                Exit For
            End If
        Next p
        If Not bSetIt Then
            ActiveWorkbook.CustomDocumentProperties.Add _
              Name:="auth", LinkToContent:=False, _
              Type:=msoPropertyTypeString, Value:=sSName
        End If
        
        Sheets(sSName).Visible = True
        Sheets(sSName).Unprotect Password:=txtPass.Text   //the debugger points to this line
        Sheets(sSName).Activate
        ActiveSheet.Cells.Locked = False
        ActiveSheet.Columns("A:E").EntireColumn.Locked = True
        ActiveSheet.Protect Password:="123"
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


        bOK2Use = True
        Unload UserForm1
    End If
End Sub










Private Sub UserForm_Terminate()
    If Not bOK2Use Then
        ActiveWorkbook.Close (False)
    End If
End Sub

 

[Jerry Sullivan]

Thanks for sending me a copy of your workbook via email.

The problem is what was originally suspected. The user-specific passwords like "Adam123" were generating error messages when the VBA code had left that worksheet with the password "123".

In the workbook you sent, sheet Loblaw has the password "123" upon opening the file.

In addition to changing this statement that you previously tried...

  ActiveSheet.Protect Password:=txtPass.Text, _
         DrawingObjects:=True, Contents:=True, Scenarios:=True

...you'll need to make sure that after making that change, each worksheet has its correct user-password instead of "123". That's a step that probably wasn't taken earlier, so the VBA fix alone didn't work.

Taking a step back to look at your approach, it doesn't appear there is any reason you need to do the steps to unprotect-unlock-relock-reprotect the user's worksheet when they access it. Your worksheet-access is happening through VBA changing the Visible state of the worksheet and it is not affected at all by whether the sheets are protected.

Is there any reason not to just eliminate all that and leave the worksheets always protected with cells A:E locked?