Unprotect/ protect

[SimonG]

'Hi, taking a sheet where all of the cells are locked, except, B3:D5, say, and then the sheet is protected with passcode '11', say, can you give me the code for a Macro to unprotect the sheet without entering the passcode the macro will then change the status of cells, B3:D5, to protected and protect the sheet with passcode '11' again but without entering the passcode. I.e. I would like the passcode to be set in the code. I would also like this macro to be activated when a 'button' in the protected sheet is 'pressed'. Thank you

 

[VoG]

Try with a button from the Controls toolbar (ActiveX control)

Private Sub CommandButton1_Click()
With Me
    .Unprotect Password:="11"
    .Range("B3:D5").Locked = True
    .Protect Password:="11"
End With
End Sub

 

[SimonG]

That worked fantastically, thank you very much, would it be possible to ask the question ‘Are you sure?” and to continue the Macro with a ‘Yes’ and exit with no change with a ‘No’?

 

[VoG]

Try this

Private Sub CommandButton1_Click()
If MsgBox("Are you sure you want to do this", vbQuestion + vbYesNo) = vbNo Then Exit Sub
With Me
    .Unprotect Password:="11"
    .Range("B3:D5").Locked = True
    .Protect Password:="11"
End With
End Sub

 

[SimonG]

Thank you very much, that all works 100%