Forum Discussion
Linked Cell for Multiple ActiveX Check Boxes
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.
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 Replies
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
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.
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.
