VBA/Macro Code for checkboxes

[craboak]

Forgive me but I am new to VBA/Macro writing for Excel.
What I am trying to accomplish:  I would like to be able to select check boxes en masse in a column and set them to link with the cells they are in.

Sub LinkChecks()
i = 2
For Each cb In Selection
cb.LinkedCell = Cells(i, "B").Address
i = i + 1
Next cb
End Sub

Where I am stuck:  I have pieced together some code that I have pulled through reading through forum posts and am pretty close to accomplishing what I need.   Essentially I have it so I can select the check boxes and get them to link  to the proper cell if they are in the "B" Column.   I can tell that this is because

cb.LinkedCell = Cells(i, "B").Address

is there  a way to get  "B"  changed in a way where it can determine what Column my selection is  in  so I don't have to go in and adjust the Code  if my check boxes happen to fall in a different column.

Context if it helps:  I tend to write spreadsheets that will have check boxes in multiple columns and would like to easily and quickly link them to the cell they are in.  I am a little more familiar with how check boxes work on Google Sheets and this is a fairly large departure from how they function in Google Sheets.  Essentially I am trying to have multiple checklists contained on one sheet.

Any help would be greatly appreciated. 

[#2 · Accepted solution]

@craboak 

You may simply try this...

Sub SetLinkedCells()
Dim cb As CheckBox

For Each cb In ActiveSheet.CheckBoxes
    cb.LinkedCell = cb.TopLeftCell.Address
Next cb

End Sub

[#3]

@Subodh_Tiwari_sktneer 

I like that this doesn't require me to directly select the boxes.  Issue I am running into with this one is it is tying it to the cell above.  Example:  Checkbox in Cell B4 is hooked to cell B3 rather than B4. 

Is there a way to adjust for that?   If so this will be perfect!

[#4]

@craboak 

That means the CheckBoxes are not inside the cell boundaries. If a CheckBox is properly inserted inside a cell, the TopLeftCell property of the CheckBox will refer to the cell it is sitting in.

In your case, you may just change the line within the For Loop to this...

cb.LinkedCell = cb.TopLeftCell.Offset(1).Address

If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.

[#5]

Ah perfect. I fixed the alignment of the boxes and your original solution worked. Thank you so much.

[#6]

You're welcome @craboak! Glad it worked as desired.