Check boxes requiring unprotected sheet, but I need the sheet protected

I have included several check boxes on a form to make automated entries, but once I got everything how I wanted it, the check boxes gave me this error:

"The cell or chart you are trying to change is on a protected sheet. To make change, unprotect the sheet. You may be required to enter a password."

Having these cells unprotected kind of makes the automation moot, since it is info that need to remain accurate. Is there any way to make the checkboxes work without unprotecting every box it affects?

This worksheet is for a small business and frankly there are too many hands in the paperwork, which is why I offered to computerize it and Excel is something I thought I was familiar with (I've come to realize I'm not as familiar as I needed to be). However, there are a couple of people who will change something just because they can and it has to be made so they can't.

By default, all the cells on a sheet are set for Locked (you can see this by following Format Cells | Protection tab). However, that protection only comes into force when the sheet is protected (on the Review tab | Protect Sheet). You can, of course, individually unlock the protection on a cell or range of cells, before you apply the overall protection, so you should do this so that only the cells that the check boxes relate to are unlocked.

Hope this helps.

Pete

I have unlocked all the check boxes, but do the cells being changed by the check box also have to be unlocked? They don't have to be with formulas or conditional formatting, and it will really diminish the security of the sheet.

Hi there,

As Pete says, just set the "Locked" property of the CheckBox Link Cells to FALSE (i.e. unticked on the Format Cells > Protection > Locked DialogBox), and then protect the worksheet. In this case, the worst that can happen is that a User types TRUE or FALSE into one of the link cells.

Regards,

Greg M

OK, thanks guys. I just moved the cells being referenced down about 40 rows below the form and left them unlocked and white text so it doesn't show. I doubt any of these folks will look that hard for them.

Hi again,

Thanks for your feedback.

left them unlocked and white text so it doesn't show

Just for information, setting the NumberFormat of a cell to ";;;" (without quotes) is probably a nicer way of hiding its contents.

Regards,

Greg M