Forum Discussion
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 realized if I want to do this one by one by the time I finish it, I will be homeless. I also tried some VBA codes but they mostly work for only one column, not multi-column. like this one below; Is there any solution to this problem that somebody can help or I should start applying for a new job?
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
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- Riny_van_Eekelen
Thank you very much for considering my problem and for taking the time to help me. I looked at your profile, very impressive. All my family lives in Sweden Gothenburg. Again appreciate your help. JMB17 solution is very straightforward. I realized you have resolved so many problems regarding Checkboxes' subject. because before I ask my question I search a lot to see maybe somebody else had a same problem before and I see you almost were engaged in most of them. I think you should check out JMB17 Solution. you might refer it later to somebody else like me. - After searching for many codes out there, you saved my life with that. Thanks so much!!!!
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- Great it worked! Thanks JMB17
- JMB17
Thank you very much for your efforts and for taking the time to solve my problem. You helped me before too. I remember your Id. You are a truly intelligent man. Again appreciate it a lot, it could not be more clear and straightforward on the point. It works and this solution was exactly the one I needed.
