Macro for automatically linking Multiple ActiveX Checkboxes in one cell

[heroesoflegend]

Hi Enthusiasts,

I have one excel cell with 25 ActiveX check boxes. I have manually associated each checkbox in row 3 with a cell (i.e. the Microsoft Word checkbox in row 3 is associated with CF3, the Microsoft Excel checkbox is associated with CG3 etc). I have dozens of rows of Official Record Examples (column C). Is there a way to 1) easily copy down the 25 check boxes to each row of column AA, and 2) automatically link the check boxes to the appropriate cell in their row (i.e. Microsoft Word checkbox for row 4 would should be linked to CF4, and its checkbox for row 5 should be linked to CF5). I can't simply link every checkbox to a certain number of cells away due to having multiple check boxes sharing one cell (and each checkbox needing to link to its own cell). I'm relatively new at this, so any advice would be appreciated! Thanks and have a great day!

 

[Darren Bartrup]

One possibility is the code below.
This will go through each checkbox on the sheet and adjust the linked cell so it looks at the current row.

Try on a copy of your worksheet first - I haven't done much testing, so could fall over.

Sub Test()
    Dim wrksht As Worksheet
    Dim chk As Object
    Dim rng As Range
   
    Set wrksht = ThisWorkbook.Worksheets("Sheet1")
   
    For Each chk In wrksht.OLEObjects
        If TypeName(wrksht.OLEObjects(chk.Name).Object) = "CheckBox" Then
            Set rng = Range(chk.LinkedCell) 'NB: This will fail if the checkbox doesn't have an address.
            chk.LinkedCell = Cells(chk.TopLeftCell.Row, rng.Column).Address(False, False)
        End If
    Next chk
End Sub

To copy the checkboxes you could select each one while holding Ctrl and then group them (right-click, select Group), you can then copy and paste the group.