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, Nov 18 '15 at 21:42
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, Nov 18 '15 at 22:01
1) the error occurs where you have listed, 2) "locked" is the password — ejdav, Nov 18 '15 at 22:05
@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, Nov 18 '15 at 22:06
Something does not makes sense here I tried the code with the `ActiveWorkbook.ActiveSheet.Cells.Locked = True` in the right place and it works, so please bear with me: 1) Is this button in the same worksheet you want to unprotect? 2) Do you mind editing your code and placing this line `ActiveWorkbook.ActiveSheet.Cells.Locked = True` as it was at the time of the error in your code? thanks... — EEM, Nov 18 '15 at 22:28
I now think my issue is related to cells being merged in the worksheet, I am exploring un-merging the cells before locking the cells. I see some advice on locking merged cells but I can't make it work. http://stackoverflow.com/questions/9184818/error-1004-when-setting-range-locked — ejdav, Nov 19 '15 at 14:32

score 1 · Answer 1 · answered Nov 18 '15 at 21:33

1

Is this what you mean:

ActiveWorkbook.ActiveSheet.Cells.Locked = True

This will set all the cells in the sheet to locked

answered Nov 18 '15 at 21:33

area9

391
2
12

I tried that and got a run time error 1004: unable to set the locked property of the range class – ejdav Nov 18 '15 at 21:39
1

Did you have the sheet unprotected first? – area9 Nov 18 '15 at 21:41
yes and I also unlocked the protection on the workbook structure – ejdav Nov 18 '15 at 22:02

score 0 · Accepted Answer · answered Nov 19 '15 at 21:36

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 Answers2