Checked Checkbox locks the linked cell

[RokTikiTiki]

Greetings everyone,

I have one final thing that I am trying to do on what will be a shared Excel file to prevent others from potentially changing or manipulating the information that is being entered.

I have a list of requests that come through and each line gets a checkbox added. When this checkbox is checked, it generates a time stamp and crosses the request off of the list. The sheet is protected and the boxes can be checked by keeping the linked cells unlocked. However, what I am trying to do is lock those linked cells once the checkbox is checked. This would prevent the checkboxes from being un-checked. I have searched for hours and can't figure out a way to make this happen. Ideally it would just be a sub that I can call in my current VBA code which unprotects then re-protects my sheet. Any advice would be greatly appreciated.

I am using Excel 2016. The checkboxes are Form Controls, but I would be willing to change to ActiveX Controls if needed (but would hope I wouldn't have to ).

 

[RokTikiTiki]

So I wasn't able to exactly figure this out, but I was able to get a solution that works. All of the checkboxes and linked cells are in column H for me. So I put this code in there

Sub lockCell()Dim wkSheet As Worksheet
Dim rng As Range, rCell As Range

Set wkSheet = Sheets("Sheet1") '-- use your own sheet
Set rng = wkSheet.Range("$H$2:$H2000") '-- use your own range

For Each rCell In rng
    If rCell.Offset(0, 0).Value = "True" Then
        rCell.Offset(0, 0).Locked = True '-- here we are locking the adjacent cell
    Else
        rCell.Offset(0, 0).Locked = False
    End If
Next rCell

End Sub

This goes through and locks cells that are marked TRUE in that range. I then call this in the existing code for the button check after I unprotect the page and before I reprotect the page. So not exactly what I was looking for, but accomplishes the task and possibly better than I could have hoped for.

 

[Greg Truby]

If rCell.Offset(0, 0).Value = "True" Then

• You don't need the Offset(0,0)
• Is the value "True"? Or is the value TRUE? (Is it a string or a boolean). VBA will coerce the value to match the test's type, so either will actually pass. But generally it's better to match type.

...I unprotect the page and before I reprotect the page.

If you initially impose the protection via code and not the UI, you can set the UserInterfaceOnly argument to TRUE. Then you needn't toggle protection if making changes via code.

    Worksheets(1).Protect UserInterfaceOnly:=True

rCell.Offset(0, 0).Locked = True '-- here we are locking the adjacent cell

Nope. Yer lockin' the same cell, not the adjacent cell. (Unless, by "adjacent cell", you meant "the cell adjacent to the checkbox" and not "the cell adjacecent to rCell".)

And, overall, you're making this harder than it needs to be. Assuming you do indeed wish to lock based on value of adjacent cells:

    With Range("H2:H2000")
        .Locked = .Offset(, -1).Value     '// locking cells in H based on values in G
    End With

If yer wantin' ta lock the same cells (and if they're the linked cells, then I reckon ya probably are) then it's just:

    With Range("H2:H2000")
        .Locked = .Value
    End With