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 · Accepted Answer · 2018-05-07 13:51:18Z

2

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.

edited May 7, 2018 at 13:51

answered May 7, 2018 at 11:07

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ema Over a year ago

Thanks Vityata for your reply, but I think I did not explain well what the issues is. The macro above works with no problem, i.e. it performs the hiding/unhiding of columns. I added an explanation to the original post, it may clarify the problem.

Vityata Over a year ago

@Ema - I see. What would happen if you use only the code in my answer. Would you be able to protect/unprotect the sheet manually after this? Are you using multiple keyboard layouts, except for English?

Ema Over a year ago

it works in the sense that I can protect/unprotect the sheet both manually and via VBA - although this is not linked to a click event. But I also noticed that it protects the sheet with any psw other than "xyz", that may lead the user to not being able to work on the sheet anymore...

Vityata Over a year ago

@Ema - you can always play a bit around. Now, if the password is not xyz but anything else, it is set to xyz1. And the user needs to give xyz manually. In general, it depends on the business logic you want to follow.

Ema Over a year ago

@Vitayta, problem solved: I copied all the values and re-written the same code, it works perfectly know and has no issue when protecting/unprotecting both manually or via VBA. Maybe because the file was produced in older versions of Excel?

|

todbott · Accepted Answer · 2020-07-10 05:51:23Z

0

I just had this same problem, and thought I'd share what I found. The problem is, colons should be used when protecting/unprotecting sheets via script:

Worksheets("Multiregional").Protect Password = psw
Worksheets("Multiregional").Unprotect Password = psw

should be

Worksheets("Multiregional").Protect Password := psw
Worksheets("Multiregional").Unprotect Password := psw

answered Jul 10, 2020 at 5:51

todbott

6417 silver badges10 bronze badges

Collectives™ on Stack Overflow

Password VBA vs. manually typed do not match

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related