How to link multi checkbox list for multi columns HELP

[msm66]

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

[#2]

@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

[#3 · Accepted solution]

@msm66 

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

[#4]

@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.

[#5]

@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.

[#6]

@msm66

You're quite welcome. I'm happy that worked for you.

[#7]

@msm66 

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.

[#8]

@msm66 

So, you want the green table to pull from the first table depending on whether or not that day/task is checked?

I believe it is doable (see attached).

[#9]

@JMB17 

Well thank you, I would never figure this out by myself. 😉

it is working perfectly. thank you. 

only one question that might happen in the future., let's say we have multi-columns under each day. for example, underneath the Monday we have 10 columns and 4 rows. if we use this formula then we will see "off" in each cell and it won't look good. 

is there any trick that the formula merges all the cells and just we see one "off" instead of multi off?

thank you. 

[#10]

I'm afraid formulas can't perform actions like merging cells.

[#11]

Great it worked! Thanks @JMB17

[#12]

After searching for many codes out there, you saved my life with that. Thanks so much!!!!