Strange issue with sheet protection macro

[Fishboy]

Hi all,

I have been playing around with some ProtectSharing macros in my Excel 2010 workbook. I know Excel passwords are not worth the time if the user really knows what they are doing - but these users will not be a threat to the integrity of the password.

I have made 3 macros for testing:

This one enables ShareProtecting with my desired password, then updated cell B1 with a 1

Sub ProtectSharingOn()
    strResponse = InputBox("Please enter the password.", "Password")
        If strResponse = "MyPassword" Then
            Application.DisplayAlerts = False
                ActiveWorkbook.ProtectSharing Password:=strResponse, _
                    SharingPassword:=strResponse
                        Sheets("Master Sheet").Range("B1").Value = 1
            Application.DisplayAlerts = True
        Else
            MsgBox "The password entered is incorrect.", vbCritical, "Incorrect Password!"
        End If
End Sub

This one disables ShareProtecting with my desired password, then updated cell B1 with a 0

Sub ProtectSharingOff()
    strResponse = InputBox("Please enter the password.", "Password")
        If strResponse = "MyPassword" Then
            Application.DisplayAlerts = False
                ActiveWorkbook.UnprotectSharing
                    Sheets("Master Sheet").Range("B1").Value = 0
            Application.DisplayAlerts = True
        Else
            MsgBox "The password entered is incorrect.", vbCritical, "Incorrect Password!"
        End If
End Sub

Finally I was playing around with a toggle between the 2 based on whether or not there is a 1 in cell B2

Sub ToggleShareProtection()
    If Sheets("Master Sheet").Range("B1").Value <> 1 Then
        Call ProtectSharingOn
    ElseIf Sheets("Master Sheet").Range("B1").Value = 1 Then
        Call ProtectSharingOff
    End If
End Sub

Now these all work fine and dandy in and of themselves, but the strange behaviour I have seen is that even AFTER disabling ShareProtection, if I save and close the file I am prompted for the password when the document opens and this is not something I had anticipated. Obviously I know the password so that side of things isn't an issue, however if a user has to enter the password to open the file themselves then they are already armed with the password to toggle the protection as well.

It seems even if I manually run each of the first 2 macros to turn ShareProtecion on and off, after saving and closing the file wants a password on reopening.

Does anyone have any ideas what might be causing this, or how I might negate the issue please?

 

[Richard U]

2010 is buggy as all hell.

Try copying that code into notepad++ and then into another workbook and see if you still have that issue.

If not, then there is corruption in one of your modules. I have had similar things happen to me.