VBA/Macro Code for checkboxes

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.

excel

Macros and VBA

Subodh_Tiwari_sktneer

Aug 18, 2021

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

5 Replies

Subodh_Tiwari_sktneer
Silver Contributor
Aug 18, 2021
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
- craboak
  Copper Contributor
  Aug 18, 2021
  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!
  - Subodh_Tiwari_sktneer
    Silver Contributor
    Aug 18, 2021
    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.

Forum Discussion

VBA/Macro Code for checkboxes

5 Replies

Resources