Forum Discussion
Solved
Format ALL checkboxes to link to adjacent cells w/o having to Format Control all individually?
There are 96 spreadsheets. Each spreadsheet has 60 checkboxes. I need to find all of the boxes that were checked and calculate various things from there. But first step's first, link each checkbox with the cell next to them to create TRUE/FALSE values. I understand the latter portion and how to deal with the data once the TRUE/FALSE values are there. What I'm struggling with is how long and tedious it is to link each cell individually with the one next to it.
My current process:
1) Create a new column next to the checkbox cells
2) Right click a checkbox and click Format Control
3) Link to the cell next to it
This is taking forever! I feel like I'm not doing this efficiently, and maybe there is a much more straightforward way.
I know NOTHING about coding. Is there some code I could use for each spreadsheet that makes it so all the cells link to the cells next to them, without having to do each one individually?
Thank you in advance! New to all of this.
My example sized the checkboxes at 30 pixels high, starting at (0,0) i.e. top left of the sheet, and with a row height of 30 as well. Play around a little with the parameters to make it match your requirements.
try this code
Sub CreateCheckboxes() Dim cell As Range Dim chkBox As CheckBox Dim ws As Worksheet Set ws = ActiveSheet For Each cell In Selection ' Calculate the position of the checkbox Dim leftPosition As Double Dim topPosition As Double Dim chkWidth As Double Dim chkHeight As Double chkWidth = 15 chkHeight = cell.Height / 2 leftPosition = cell.Left + cell.Width / 2 - chkWidth / 2 topPosition = cell.Top + (cell.Height / 2) - (chkHeight / 2) ' Add checkbox and set its properties Set chkBox = ws.CheckBoxes.Add(leftPosition, topPosition, chkWidth, chkHeight) With chkBox .Caption = "" .Value = xlOff .LinkedCell = cell.Offset(0, 0).Address .Display3DShading = False End With ' Hide the value in the linked cell cell.Offset(0, 0).Value = "False" cell.Offset(0, 0).NumberFormat = ";;;" Next cell End Subyou can see the result, the cells you select is added with checkboxes, and the checkboxes is linked to each of the cells, and the value(it should be "true" or "false") of the cells is hide.
the result
linked to the original cells, and the value of cells is hide
You ask how to automate the creation of checkboxes and linking cells to these checkboxes. The topic below includes a macro i created. It creates checkboxes (how many do you want?), names them and links each of them to a cell. Perhaps this gets you started. You need to scroll down a bit, though.
https://techcommunity.microsoft.com/t5/excel/copying-a-check-box-to-other-cells/m-p/1091496
Thank you Riny_van_Eekelen! I think this will work. It will be faster to just do this and manually recheck them than having to individually format. Just one question. It looks like the checkboxes skip a line, but the linked cells do not, so they separate. Is there anyway I can adjust the cells to keep them next to each other? (See image below.) Thanks again for your help.
My example sized the checkboxes at 30 pixels high, starting at (0,0) i.e. top left of the sheet, and with a row height of 30 as well. Play around a little with the parameters to make it match your requirements.
