Password VBA vs. manually typed do not match

Question

I have an ActiveX Toggle button in a protected sheet with password. When I want to trigger an action, it does need to unprotect the sheet.

What happens is that the same password that has been inserted manually by right-clicking on the sheet name>>Protect Sheet... does not work if I use VBA.
The same is true the other way round: if I protect the sheet using VBA, then I am not able to unprotect it manually by right-clicking on the sheet name>>Protect Sheet...

Here is my code:

Private Sub ToggleButton1_Click()
On Error GoTo Error_handler

psw = InputBox("please enter the password")

If ToggleButton1.Value = False Then
    Worksheets("Multiregional").Unprotect Password = psw
        ToggleButton1.BackColor = vbGreen
        Call hide_columns 'this filters row and hides a few columns
    Worksheets("Multiregional").Protect Password = psw, DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False, _
        AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

ElseIf ToggleButton1.Value = True Then
    Worksheets("Multiregional").Unprotect Password = psw
    ToggleButton1.BackColor = RGB(204, 204, 204)
    Call show_everything 'this unhide and unfilter everything
    Worksheets("Multiregional").Protect Password = psw, DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingColumns:=False, AllowFormattingRows:=False, _
        AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End If

Exit Sub
Error_handler:
MsgBox Err.Description

End Sub

I tried these two ways to insert a password, but NONE of the two works;

using inputbox (as above: psw = InputBox("please enter the password") and then Worksheets("Multiregional").Unprotect Password = psw
typing the psw in the code Worksheets("Multiregional").Unprotect Password = "xyz"

EDIT:

What does not work is unprotecting the sheet in a different method than the one used to protect it (and the other way round), because for some reason the same psw entered via VBA or manually seems to be not recognised as the same string by Excel. This results in the fact that:
1) If I set the psw via VBA, I cannot manually unprotect the sheet and, conversely,
2) if I set up the psw manually, I cannot use the macro.
In both cases the error message is that the psw is not correct.

Do you have a solution or experienced the same problem before?

Thanks to all of you in advance for your kind support!

Vityata Vityata · Accepted Answer · 2018-05-07T11:07:53

Start from something really small. E.g. try this:

Public Sub TestMe()

    Dim psw As String
    psw = InputBox("please enter the password")
    MsgBox psw   'to see what you have entered

    If psw = "xyz" Then
        Worksheets(1).Unprotect "xyz1"
    Else
        Worksheets(1).Protect "xyz1"
    End If

End Sub

If the password is xyz then unprotect, otherwise - protect. Once this works, you can continue building further logic.

Password VBA vs. manually typed do not match

2 Answers