Selected Answer
The code below will reset the LinkedCell property of check box on the specified sheet. Install the code in a standard code module (none of the code sheets in a workbook by default - you have to add it to a virgin workbook, by default Module1). Please set the four constants at the top of the code to the appropriate values. Run the Sub AssignLinkedCell.
Option Explicit
Sub AssignLinkedCell()
' 07 Jun 2019
' enter equal number of CheckBoxColumns and LinkedCellColumns,
' their position in the string matching, comma-separated
Const CheckBoxColumns As String = "L,M"
Const LinkedCellColumns As String = "O,P"
Const SheetName As String = "Sheet2"
Const FirstCheckBoxRow As Long = 12
Dim Ws As Worksheet
Dim CBox() As Long
Dim CLnk() As Long
Dim Chk As Shape
Dim Ole As Object
Dim i As Integer
Dim R As Long
Set Ws = ActiveWorkbook.Worksheets(SheetName)
CBox = ColumnsArray(CheckBoxColumns)
CLnk = ColumnsArray(LinkedCellColumns)
For Each Chk In Ws.Shapes
Set Ole = Chk.OLEFormat.Object
If TypeName(Ole) = "CheckBox" Then
With Ole
i = Application.Match(.TopLeftCell.Column, CBox, 0)
.LinkedCell = Cells(.TopLeftCell.Row, CLnk(i - 1)).Address
End With
End If
Next Chk
End Sub
Private Function ColumnsArray(Clms As String) As Long()
' 07 Jun 2019
Dim Fun() As Long
Dim Sp() As String
Dim i As Integer
Sp = Split(Clms, ",")
ReDim Fun(UBound(Sp))
For i = 0 To UBound(Sp)
Fun(i) = Columns(Trim(Sp(i))).Column
Next i
ColumnsArray = Fun
End Function
Note that you can enter as many CheckBoxColumns as you wish. Checkboxes found in columns which aren't listed will not be reset. If there are fewer columns listed in the LinkedCellColumns string than in the CheckBoxColumns string a fatal error will occur.
As for your CF, note that you need not specify an absolute reference to the linked cell. In the attached workbook I selected all cells with checkboxes in column L and clicked to add a new rule. I then entered the formula =$O12=TRUE, making only the column absolute. Excel applied the reference offsets to each row automatically.
Let me caution you that the code may not run on your Exam worksheet. Perhaps the copy I downloaded is corrupt but I believe it should be your original as well. I was unable to fix it. Therefore I tested the code on a second sheet which I prepared especially. The first sheet in the attached workbook is also corrupt. I suspect that there are duplicate text boxes in identical locations - a result of incomplete deletions. However, the reason could also be that you established so many CF rules, one for each cell. There might be confusion somewhere due to the copying you did.
Frankly, I don't believe you will ever get your workbook to work smoothly. 7200 checkboxes are just too many. I think they can all be avoided. Your columns H and J appear designed to take test results. If a test result is entered a test was carried out. Therefore the checkbox certifying that fact seems superfluous. Just use CF to mark the cells with results in them or create adjacent cells with text like "tested". A formula can do that without user action. The checkboxes seem to have not much purpose other than keep the user busy - and slow down your workbook.
