Automatically update cell link of activex checkboxes

[Empirer]

Hi All,

Would like your help on a vba code that would update the cell link of activex checkboxes for each row.
I currently have 3 checkboxes in AL2, each of the checkboxes link to a different column; first checkbox links to column AN2, second links to AO2, third links to AP2.
When copying down the checkboxes up to row 100, the cell links remains linked to row 2.

Kindly assist in providing a vba to update these cell links.

 

[Crystalyzer]

have a go at this

Sub LoopThroughCheckboxes()

'Create variable
Dim chkBox As CheckBox
Dim r As Range, r2 As Range

'Loop through each check box on active sheet
For Each chkBox In ActiveSheet.CheckBoxes

    With chkBox
        Set r = Range(.TopLeftCell.Address)
        Set r2 = Range(.LinkedCell)
        .LinkedCell = r.Offset(, r2.Column - r.Column).Address
       
    End With
Next chkBox

End Sub

 

[Empirer]

have a go at this

Sub LoopThroughCheckboxes()

'Create variable
Dim chkBox As CheckBox
Dim r As Range, r2 As Range

'Loop through each check box on active sheet
For Each chkBox In ActiveSheet.CheckBoxes

    With chkBox
        Set r = Range(.TopLeftCell.Address)
        Set r2 = Range(.LinkedCell)
        .LinkedCell = r.Offset(, r2.Column - r.Column).Address
      
    End With
Next chkBox

End Sub

Unfortunately, this code doesn't work.

Maybe the issue is that the checkboxes in the subsequent rows are considered to be the same as in row 2. So whenever I tick a box in row 3, it will also update the checkbox in row 2 and all the other rows. It does not see them as individual checkboxes.

 

[Crystalyzer]

the code loops through all checkboxes on the active worksheet, checks their TopLeftCell location and adjusts the LinkedCell to the row of the TopLeftCell and the column of the LinkedCell (eventhough the linked cell's row is incorrect we use the column).

Can you post a picture of your worksheet showing the position of the checkboxes?

 

[NoSparks]

Crystalyzer's code works, just overlooked that the check boxes are ActiveX controls
Try this

Sub LoopThroughCheckboxes()
'Create variable
Dim chkbox As Object
Dim r As Range, r2 As Range
'Loop through each check box on active sheet
For Each chkbox In ActiveSheet.OLEObjects
    If TypeName(chkbox.Object) = "CheckBox" Then
        With chkbox
            Set r = Range(.TopLeftCell.Address)
            Set r2 = Range(.LinkedCell)
            .LinkedCell = r.Offset(, r2.Column - r.Column).Address
        End With
    End If
Next chkbox
End Sub

 

[Empirer]

Crystalyzer's code works, just overlooked that the check boxes are ActiveX controls
Try this

Sub LoopThroughCheckboxes()
'Create variable
Dim chkbox As Object
Dim r As Range, r2 As Range
'Loop through each check box on active sheet
For Each chkbox In ActiveSheet.OLEObjects
    If TypeName(chkbox.Object) = "CheckBox" Then
        With chkbox
            Set r = Range(.TopLeftCell.Address)
            Set r2 = Range(.LinkedCell)
            .LinkedCell = r.Offset(, r2.Column - r.Column).Address
        End With
    End If
Next chkbox
End Sub

Thanks for the reply, however it still didnt work. Same issue like before, whenever a checkbox is ticked, it will update all the other checkboxes as well and the linked cell remains in the second row only.

 

[NoSparks]

Works for me.
Can troubleshoot a sheet but not a picture.

Merged cells ?

 

[Empirer]

Works for me.
Can troubleshoot a sheet but not a picture.

Merged cells ?

I'm unable to add in activex checkboxes in the minisheet, checkboxes were originally in AL:

FIG WFS Offline QC Tracker.xlsm
	AL	AM	AN	AO	AP
1	Inline Quality Control Trend	Analyst Control Comments	Filter Checkbox Cornerstone	Filter Checkbox Docs	Filter Checkbox Other
2			TRUE	FALSE	FALSE
3
Sheet1

 

[NoSparks]

You could share a file from Google Drive, One Drive, Dropbox or the one I use
box.com, it's free and easy.

 

[Empirer]

You could share a file from Google Drive, One Drive, Dropbox or the one I use
box.com, it's free and easy.

Good suggestion, kindly find the excel file attached here:

FIG WFS Offline QC Tracker.xlsm

1drv.ms