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

 

[mole999]

use UCASE or LCASE to force the code to be always the correct format, regardless of how its entered, that will save you complaints that it dosen't always work

 

[Jerry Sullivan]

Welcome to MrExcel,

The code appears to be re-applying the worksheet protection with the password "123" after the user successfully unhides the sheet. Do you have code elsewhere that resets the password to the user's (e.g. "Adam123")?

 

[born2run]

Welcome to MrExcel,

The code appears to be re-applying the worksheet protection with the password "123" after the user successfully unhides the sheet. Do you have code elsewhere that resets the password to the user's (e.g. "Adam123")?

thanks Jerry . when user sees the authorized tab , the column A to E is locked and the password 123 is used to unlock the column A to E. I used to get the error even before I added the code to lock columns. do you see anything wrong with the code debugger points to (highlighted in bold in the original message)?

 

[born2run]

use UCASE or LCASE to force the code to be always the correct format, regardless of how its entered, that will save you complaints that it dosen't always work

thanks . a great idea . will try this out and let the forum know .

 

[Jerry Sullivan]

thanks Jerry . when user sees the authorized tab , the column A to E is locked and the password 123 is used to unlock the column A to E. I used to get the error even before I added the code to lock columns. do you see anything wrong with the code debugger points to (highlighted in bold in the original message)?

The password is being used to Protect and Unprotect the entire worksheet. There isn't a separate password for unlocking Columns A:E.

I believe the problem is that the first time the user enters their password "Adam123", this part of the code works as expected, however it reapplies protection of the worksheet with the password "123".

        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"

The next time that Adam runs this code, it tries to unprotect the worksheet using his entered password "Adam123" and it generates the error because Excel is wanting "123" to unlock the sheet.

You noted that the problem arose before the Columns("A:E").EntireColumn.Locked line was added. That could be the case if you previously had the Password:="123" part when the problem first occurred.

The simple fix would be to modify this line to...

 ActiveSheet.Protect Password:= txtPass.Text

 

[born2run]

The password is being used to Protect and Unprotect the entire worksheet. There isn't a separate password for unlocking Columns A:E.


The simple fix would be to modify this line to...

 ActiveSheet.Protect Password:= txtPass.Text

I tried both options - replacing password 123 with txtPass.Text and converting entered password to lower/uppercase . but they didn't work . I have attached the updated code. May be if I can upload the workbook , it would be easier but don't see an easy way to upload ?

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
    txtPass.Text = LCase(txtPass.Text)
    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
        Sheets(sSName).Activate
        ActiveSheet.Cells.Locked = False
        ActiveSheet.Columns("A:E").EntireColumn.Locked = True
        ActiveSheet.Protect Password:=txtPass.Text
        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

 

[born2run]

not sure if it helps but all the logic built in the workbook works as expected and this error doesn't stop any part of code from executing . however, it is annoying for users to see the error even if they enter correct user id & password. look forward to your comments.

 

[Jerry Sullivan]

Try combining the 2 statements you currently have to this one statement:

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

 

[born2run]

I combined both the statements as per your suggestion but still no luck . .

Try combining the 2 statements you currently have to this one statement:

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