VBA to change cell protection format from unlocked to locked

Question

My worksheet contains cells that are locked to protect formulas and unlocked to allow data entry. At the end of the worksheet is a sign-off button for a reviewer. I want to add VBA to this button that will lock the previously unlocked cells (by cell format) to prevent changes after the review is complete for the worksheet.

Here is my current code -

Sub Button9_Click()
If Range("I108") = Empty Then
If MsgBox("Are you sure you want to sign-off COMPLETE as Reviewer?", vbYesNo) = vbNo Then Exit Sub
ActiveWorkbook.ActiveSheet.Unprotect "locked"
ActiveWorkbook.ActiveSheet.Range("I109").Value = "Reviewed: " & Format(Date, "mm/dd/yyyy") & " By: " & Application.UserName
ActiveWorkbook.ActiveSheet.Protect "locked"
End If
End Sub

Run-time error '1004': Unable to set the locked property of the range class. When ActiveWorkbook.ActiveSheet.Cells.Locked = True is used — ejdav
1) At what point did you got that error, can't see the command ActiveWorkbook.ActiveSheet.Cells.Locked = True in your code. 2) is "locked" the password of the worksheet? — EEM
1) the error occurs where you have listed, 2) "locked" is the password — ejdav
@EEM and ejdav my bad I have deleted my answer. I got a phone call while reading the question and made an assumption. — Scott Craner

EEM EEM · Accepted Answer · 2015-11-19T21:36:55

The problem seems to be triggered by the Events in your workbook. try this:

Sub Button9_Click()
Const kPassWord As String = "locked"
Const kCll As String = "I109"

    With ActiveWorkbook.ActiveSheet
        If .Range(kCll) = Empty Then
            If MsgBox("Are you sure you want to sign-off COMPLETE as Reviewer?", vbYesNo) = vbNo Then Exit Sub
            Application.EnableEvents = False
            .Unprotect kPassWord
            .Range(kCll).Value = "Reviewed: " & _
                Format(Date, "mm/dd/yyyy") & " By: " & Application.UserName
            .Cells.Locked = True
            .Cells.FormulaHidden = False
            .Protect kPassWord
            Application.EnableEvents = True
    End If: End With

End Sub

VBA to change cell protection format from unlocked to locked

2 Answers