Forum Discussion
msm66
Jul 14, 2020Brass Contributor
How to link multi checkbox list for multi columns HELP
I have over 10 columns of data that each has over 50 rows. These Numbers are going up on daily bases. I have to create a checkbox list for every single Column and link it to a cell next to it. I real...
- Jul 14, 2020
Select your cells and try running this macro to add checkboxes and link it to the cell behind the checkbox.
If that's not what you want then you can change the linked cell to
cb.LinkedCell = cell.Offset(0,1).Address
to link it to the cell on the same row and one column to the right. Also, you would need to change the cell reference for the custom number format to match the linked cell (I'm assuming you want the linked cell to appear blank for a cleaner look - if not, then you can delete that line).
cell.Offset(0,1).NumberFormat = ";;;"
Sub AddCheckBoxes() Const height As Double = 19.2 Const width As Double = 17.4 Dim left As Double Dim top As Double Dim area As Range Dim cell As Range Dim cb As Object For Each area In Selection.Areas For Each cell In area.Cells left = cell.left + ((cell.width - width) / 2) top = cell.top + ((cell.height - height) / 2) Set cb = ActiveSheet.CheckBoxes.Add(left, top, width, height) cb.LinkedCell = cell.Address cb.Caption = vbNullString cb.Value = False cell.NumberFormat = ";;;" Next cell Next area Set cb = Nothing Set area = Nothing Set cell = Nothing End Sub
Riny_van_Eekelen
Jul 14, 2020Platinum Contributor
msm66 Inspired by what I found here:
https://stackoverflow.com/questions/41896374/vba-refer-to-checkbox-from-the-cell-address
Try this code:
Sub LinkChecks()
For Each cb In ActiveSheet.CheckBoxes
With cb
.LinkedCell = _
.TopLeftCell.Offset(0, 1).Address
End With
Next cb
End Sub
MinhPhuongNguyen710
Feb 24, 2023Copper Contributor
After searching for many codes out there, you saved my life with that. Thanks so much!!!!