Passworded Sheet is NOT protected

[BrianJN1]

I have a very involved Workbook which embodies many formulae.
To ensure that each worksheet and process is not inadvertently corrupted I have initiate two strategies.
The first was to construct macros which write formulae to workbooks, sometimes on demand but mostly when called by some other action, eg formulae have been deliberately overwritten by values and so need to be restored for future operations.
The second, designed to prevent any inexperienced or careless handling I have passworded every sheet in the workbook:

Sub pwd_protect()
Dim wsPass= Range("xxxxxxxxx!Yn").value [I'm really perturbed that the password is visible on a certain worksheet]
For Each ws In ThisWorkbook.Sheets
ws.Protect wsPass
next ws
End Sub

That macro works perfectly for all Worksheets except one! That I cannot understand.
Checking upon the ribbon of that sheet shows that I need to Unprotect it should I wish to edit and yet I am able.
Secondly, if I copy that worksheet directly to another new worksheet the behaviour is the same.

As a final test I created a new blank worksheet and when I ran the above macro protection was installed.

This workbook is designed so that a new 'clone' of it can be created for each new year. It is also a rewrite of last the workbook from the last year and I also note that the corresponding sheet there does not accept protection.

Before I begin to rewrite that worksheet in the new workbook I'd appreciate any advice as to what might save me some work. Many thanks for any thoughts.

 

[Michael M]

Is the worksheet that doesn't protect the one that holds the password.
Also, why not put the password into the code via InputBox....so ask the user for the password to Protect the sheets, if it doesn't agree with what is hard coded in the macro, it won't work.
Something like

Private Sub MM1()
CODEPASSWORD = "PASSWORD"
Dim PassAttempt As String
PassAttempt = InputBox("Please Enter the password to Edit")

    'if the password that is entered is not correct then diplay the following message to the user
    If PassAttempt <> CODEPASSWORD Then
        If MsgBox("The password you specified was wrong!" & vbCrLf & vbCrLf & "Would you like to try again?", vbCritical + vbRetryCancel, "Incorrect Password Entered") = vbRetry Then GoTo Retry
        Exit Sub
    End If
    If PassAttempt = CODEPASSWORD Then
        For Each ws In Worksheets
          ws.Protect CODEPASSWORD
        next ws
    End If
    
End Sub

 

[BrianJN1]

Thanks Michael, but no, the password resides on a protected sheet.
Actually, the protections have been put in place more to protect against something that I might inadvertently do in the first instance (ie a backstop) but at the same time provide against any 'fiddling' by another person who might need to use this workbook.

Since I posted I began to copy/paste smaller elements from the errant worksheet to a newly created one, one which will accept protection.
It does appear that those efforts may have been successful. My assumption is that somehow the errant worksheet has suffered a corruption (assumedly written into the 'metadata' of the sheet) and copied across when the complete sheet is copy/pasted.

I'm trusting that is the reason. Thanks.

 

[Michael M]

I know the people you may be dealing with, and I doubt there would be any effort on their part...but, you are aware that sheet protection is very weak and can be easily broken ???

 

[BrianJN1]

You're right, those folk would basically shudder at going beneath 'the skin of any computer page' thus my general lack of concern.
I am aware that such protections are weak but not sure if I've ever tried to break them other than manual brute force.