Locking cells after data entry in Excel using VBA

Question

I am trying to lock cells only if data has been entered. I want to check the range to make sure that the cells are blank prior to locking them. The error I am getting is "Unable to set Locked property of the Range class". Sheet is not protected and there are no merged cells either.

Sub Locking()
'Lock Cells if data has been entered
    Dim rpcell As Range
    Set rpcell = Range("F2:G26")

With ActiveSheet
    .Unprotect Password:="1234"
    .Cells.Locked = False
    For Each rpcell In ActiveSheet.UsedRange
        If rpcell.Value = "" Then
            rpcell.Locked = False
        Else
            rpcell.Locked = True
        End If
    Next rpcell
    .Protect Password:="1234"

End With

End Sub

@urdearboy For Each rpcell In ActiveSheet.UsedRange will apply the activesheet as the parent. — Scott Craner
Are you trying to loop through the used range or Range("F2:G26")? I think that is your issue — urdearboy
the code runs fine for me, but the line Set rpcell = Range("F2:G26") is immediately overwritten with For Each rpcell In ActiveSheet.UsedRange So it will look at all cells in the used range and not just F2:G26. — Scott Craner
If you only want F2:G26 then use For Each rpcell In ActiveSheet.Range("F2:G26") — Scott Craner

urdearboy urdearboy · Accepted Answer · 2020-02-24T15:32:38

You need to check 1 cell at a time here

Dim rpcell as Range

With ActiveSheet

    .Unprotect Password:= "1234"
    .Cells.Locked = False

    For Each rpcell In .Range("F2:G26")
        rpcell.Locked = (rpcell.Value <> "")
    Next rpcell

    .Protect Password:= "1234"

End With

Locking cells after data entry in Excel using VBA

1 Answers