CODEWITHSUNDEEP
excelvbacheckboxrowcell
HomSerein
HomSerein

Reputation: 13

How do I link my first check box with the appropriate row

I'm attempting to write a vba that automatically links checkboxes to the appropriate rows. I wrote the following

Sub LinkChecklist()

Dim chk As CheckBox

For Each chk In ActiveSheet.CheckBoxes
    chk.LinkedCell = chk.TopLeftCell.Offset(0, 1).Address
Next

End Sub

however when I run the code, the first checkbox which starts on row 2 col F, links to row 1 col G when it should be linking to row 2. This makes it so that some of the checkboxes below it link to the wrong cells also. Any tips on how to resolve that issue?

Also, would appreciate any tips on making sure that each row contains only one check box. As you can see in the image, the more text there is in a cell, the more the checkboxes get messed up. Is there any way to make it so that each row, no matter how large or small it gets, contains only one checkbox?

Thanks

image of checkboxes

Upvotes: 1

Views: 737

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

If you want to use checkboxes then it's best to use code to add them and set the properties.
For example:

'add checkboxes and link cell one column over
Sub SetUpChecks()
    Dim c As Range, cb
    For Each c In Selection.Cells
        Set cb = Selection.Worksheet.CheckBoxes.Add(c.Left + 1, c.Top + 1, 10, 10)
        cb.Placement = xlMove                   'move with cell
        cb.Caption = ""                         'no caption
        cb.LinkedCell = c.Offset(0, 1).Address  'linked cell
    Next c
End Sub

You could also write some code (triggered by the sheet activate event for example) which would loop over any existing checkboxes and make sure they're positioned correctly, by checking the LinkedCell property and re-positioning if needed.

Something like this maybe:

Sub RepositionCheckboxes()
    Dim cb, c As Range, ws As Worksheet
    Set ws = ActiveSheet
    For Each cb In ws.CheckBoxes
        If cb.LinkedCell <> "" Then
            Set c = ws.Range(cb.LinkedCell).Offset(0, -1) 'one cell to the left of the linked cell
            cb.Top = c.Top + 1
            cb.Left = c.Left + 1
        End If
    Next cb
End Sub

Upvotes: 3

Related Questions