Linked Cell for Multiple ActiveX Check Boxes

[Andy Deane]

I am trying to make a large to-do list using the ActiveX checkbox feature. I am able to get line to work the way I want but when I drag or copy the line to try and repeat it all of the checkboxes are linked to the same original cell. I found how to change each checkbox linked cell individually but there has to be a better way than doing it individually several hundred times. When doing research there was a lot about how to do it with form checkboxes but not a ton on ActiveX. Can anyone help me?

PS I need ActiveX instead of form checkboxes so they can move with the lines as I minimize and maximize the groups they are in.  

[#2 · Accepted solution]

You can achieve what you have described using VBA.

Below code can do the job.  this example links the ActiveX checkboxes to Column C. you can change the column C to whatever is your real data.

I have also uploaded the sample file. when you run the macro, you will see what it does.

Sub test()
Dim sh As Shape
N = 1
For Each sh In ActiveSheet.Shapes
If sh.Type = 12 Then   ' 12 is activex checkbox
N = N + 1
sh.OLEFormat.Object.LinkedCell = "C" & N  'change C to the column of cells to be linked
End If
Next sh
End Sub

[#3]

This is great. One other quick modification I have. Would it be possible to have a break for a header and then continue the checkboxes down? They way you have it working currently works for a single long list but If i break it into several list then it no longer lines up the rows. If not the current solution will save me a lot of time as it is much easer to delete a few rather than add in a ton of new ones. Thanks for your help.

[#4]

Hi Andy,

I think you can insert all of the boxes and then insert new rows in each break point and add Header there. This way would be better than the overkill.

I am glad to hear that it worked for you.