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 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?
Sub LinkChecks()
'Update 20150310
i = 2
For Each cb In ActiveSheet.CheckBoxes
cb.LinkedCell = Cells(i, "B").Address
i = i + 1
Next cb
End Sub
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
- JMB17Bronze Contributor
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
- NegarAnsariCopper ContributorGreat it worked! Thanks JMB17
- msm66Brass ContributorJMB17
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.- msm66Brass Contributor
Hi JMB17
It's me again. since you helped with my last problem regarding checkboxes I thought you might be able to solve my recent frustration.
I attached an excel file. I am looking for a formula or function that it will adjust my calendar or schedule.
Let's say if I pick Monday only task 1, Tuesday task 1 and task 2 and Friday task 2. so now I am looking for a solution that all I can see in the new table will be look like or similar to the "green table" (basically modify a table to new table excluded the days and tasks that has not been selected (or ideally I am not sure if there is any way that just by selecting days and type of task can directly generate the table)
is this doable through excel?
looking forward to hearing your feedback.
- Riny_van_EekelenPlatinum 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
- MinhPhuongNguyen710Copper ContributorAfter searching for many codes out there, you saved my life with that. Thanks so much!!!!
- msm66Brass ContributorRiny_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.