Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

ExcelTips FAQ
ExcelTips Premium

Learn Access Now
Free Printable Forms

Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

Newest Tips

Converting to Octal

Filtering Columns for Unique Values

Printing Multiple Worksheets on a Single Page

Changing the Default Font

Creating a Drawing Object

Determining a Value of a Cell

Understanding Macros

 

Functioning Check Boxes in a Protected Worksheet

Summary: When you insert check boxes into a worksheet from the Forms toolbar, you need to link or associate the check box with a cell. If you protect the worksheet, you'll need to make sure that you explicitly unprotect both the check box and the cell to which the check box is linked. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)

Dave has developed a worksheet that uses yes/no check boxes, combined with a true/false statement to update another cell. He wants to protect the worksheet, but still be able to update the check boxes, but this doesn't seem to be possible. When he protects the worksheet, the check boxes cannot be changed.

The thing to remember in working with check boxes (from the Forms toolbar) is that there are two things you need to explicitly unprotect: the check box itself and the cell to which the check box is linked. To unprotect the check box, right-click on it and choose Format Control. Excel displays the Format Control dialog box. On the Protection tab (Click here to see a related figure.) , make sure the Locked check box is cleared.

Check boxes are also linked to specific cells in a worksheet. (See the Control tab of the Format Control dialog box.) To unprotect the cell to which the check box is linked, select the cell and choose Cells from the Format menu. Excel displays the Format Cells dialog box. On the Protection tab (Click here to see a related figure.) , make sure the Locked check box is cleared.

Now you can protect your workbook as you normally would, and the check boxes should work. The reason that you need to unprotect the cell to which the check box is linked is because the contents of the cell need to be able to change as the status of the check box is modified. If the cell is not unprotected, then the contents cannot change and so the check box cannot change.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3078) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books.
 
Check out ExcelTips Premium today!