Protecting Worksheets

Tips, Tricks, and Answers

The following articles are available for the 'Protecting Worksheets' topic. Click the article's title (shown in bold) to see the associated article.

Fixing Macro Button Behavior in Protected Worksheets
If circumstances are just right, you can end up with an ineffective macro button when you protect a worksheet. This tip explains the circumstances, what causes the problem, and how you can go about fixing it.

Functioning Check Boxes in a Protected Worksheet
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.

Hiding and Protecting Columns
Excel allows you to protect, to a degree, the contents of various cells in your worksheets. If you want to protect an entire column, there are specific steps you should take to make sure that users can't see what is in the column. This tip explores those steps, along with the caveats of how far the protection actually extends.

Inserting and Deleting Rows in a Protected Worksheet
You've protected and saved your worksheet with explicit instructions that you be allowed to insert and delete rows. But when you try it, you find that you can't get rid of rows you want to remove. This is due to the way that Excel interprets your protection instructions, as explained in this tip.

Locking Worksheet Names
Want to stop other people from changing the names of your worksheets? You can provide the desired safeguard by using the workbook protection features built into Excel.

Preventing Someone from Recreating a Protected Worksheet
When you share a protected workbook with other people, you may not want them to get around the protection by creating a new workbook from scratch. Here's some ideas on how to avoid this end-run on your work.

Protecting a Single Worksheet
Excel allows you to protect your worksheets easily—and that includes if you need to protect only a single worksheet out of an entire workbook. Here's a high-level discussion of how to achieve your protection needs.

Protecting Individual Worksheets, by User
Excel allows you to protect worksheets, individually. Users can still see that the worksheets are they, but they cannot display them if they don't know the proper password. If you are developing a full-blown application in Excel, you may need a way to completely hide worksheets from users unless they enter their password. This tip explains the steps necessary to implement such a scenario.

Protecting Worksheets
Excel allows data protection for particular cells or a whole worksheet in a shared work environment.

Protecting Worksheets from Deletion
If you share a workbook with others in your office, you will probably want to make sure that some of the worksheets don't get deleted. Here are some ideas on how to get the protection you need.

Selecting Combo Boxes in Locked Worksheets
Once you protect a worksheet, you may run into problems with any combo boxes that the worksheet contains. This is a simple problem to fix, as described in this tip.

Sorting Data on Protected Worksheets
Protect a worksheet and you limit exactly what can be done with the data in the worksheet. One of the things that could be limited is the ability to sort the data. This tip explains how you can allow that data to be sorted.

Spell-Checking in a Protected Worksheet
When you protect a worksheet, you can't use some tools, including the spell-checker. If you want to use it, you must unprotect the worksheet, run the check, and then protect it again. All of this can be done quite quickly by using the macros discussed in this tip.

Unlocking a Worksheet with an Unknown Password
Excel uses several different types of security for data in a workbook. At a worksheet level, you can protect the contents of the sheet so they cannot be changed. There are ways around this protection, however, and this tip discusses them.

Using a Protected Worksheet
If you have a worksheet protected, it may not be immediately evident that it really is protected. This tip explains some of the tell-tale signs you can use to determine if protection is in play.

Visually Showing a Protection Status
Need to know if your worksheets or workbook are protected? By implementing just a quick little macro, you can have that information deposited directly into your worksheet.