CHECKBOX in cells

Hi

I am trying to place checkboxes in multiple cells (for an attendance tracker) but would like to do this without using the developer tab and Form Control (but using Cell Control). I would simply like users to click on the checkbox (which puts a tick in it) as I have a final column which calculates a % of how many ticks ie. completed tasks. I don't think I can calculate percentages if I use Form Control via the developer tab.

I cannot find anywhere on my version of Excel how to do it. There used to be a 'checkbox' option (next to comment, link etc.) when you clicked on the INSERT tab on the Excel ribbon but it's not there now.

I have tried other ways like data validation but there's no option for checkbox on that either.

Can anyone help me? I've attached an image of where the CheckBox is/was supposed to be ... but it's not on my ribbon.

TIA

It's not on my ribbon either. Please update your profile to indicate the version of Excel you are using.

If you insert a Form control (or ActiveX control) you can link it to a cell, so the cell is TRUE if the box is ticked, and FALSE if not. Then you can calculate percentage by counting TRUE. No VBA is required.

Your checkboxes were not linked to cells. I did it for you with a little code.

An alternative to checkboxes is to use the Double click event to enter/delete a character in the cell.
The cells are formatted with Marlett font and the doubleclick event enters or removes "a" which appears as ✔

dynamic named range:
Checkboxes =OFFSET(Sheet1!$A$2,1,1,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$2:$2)-1)

It's a currently only available in certain versions
https://insider.microsoft365.com/en-...boxes-in-cells

Thanks a lot!

Do you mind explaining exactly what you did as I can't see the different between the formula for e.g. candidate 13 and the rest. (Candidate 13 doesn't work as I think you left it unlinked). How do you link to cells?

Can I just copy a line to create a new line for a candidate or do I need to do something else.

When I copy and create new rows and click on one check box the whole column gets ticked ??

I need to be able to transfer this to my master sheet.

Thanks again

I used some code to do it, and the code looks at the positioning of the check box and links it appropriately. However, sometimes the position of the checkbox is not precise and it gets linked to the wrong cell. Not sure how an entire row was missed.

To do it manually, right-click on the checkbox, select Format Control, go to tab Control. Enter the cell address in "Cell link" or click in the box and then click on a cell.

I will provide the code below if you are interested. I got it from this Microsoft community page.

I thought this was your actual file. How is your master sheet different than what you attached?

Also,

I cannot find anywhere on my version of Excel how to do it.

please update your profile to show your version of Excel.

Please Login or Register  to view this content.

The code will work on whatever cells are selected when you run the code.

See also post #5 above for a solution that doesn't even need checkboxes. Frankly that's how I would do it. (You could also use the SelectionChange event for a single click, vs. double click).

See screenshot for version (Microsoft 365) and the actual update version (yours is 2207)
Excel version.jpg

For single click use SelectionChange instead of BeforeDoubleClick. There is no Cancel argument.

Please Login or Register  to view this content.

Originally Posted by F1ash

I didn't realised it'd be a problem once uploaded an running on a browser.

I didn't realize you were running on a browser. This is why I keep asking what version of Excel you use.

VBA will not run in the browser version of Excel.

You can still use VBA to link the checkboxes, and then open that file in the browser version.

The formula is wrong. Do a global Find & Replace to change

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

I did not try your file in Excel for the web. I knew VBA would not run but I thought checkboxes with no code would be OK. Sorry you hit that wall.

I don't know any alternative other than just typing X into the boxes and COUNTIF(A5:E5,"X").

I still need you to update your profile with your Excel version.