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.
Checking Lock Status of Cells
When you first create a worksheet, all the cells in that worksheet are formatted as locked. As you unlock various cells on the worksheet, you can easily lose track of which cells are locked and unlocked. Here’s some ideas on how you can easily check the lock status of individual cells.
Controlling Entry Order on Unprotected Cells
When you protect a worksheet, one of the benefits is that you can limit which cells can be used for data entry. How a user moves from cell to cell is controlled by Excel. If you want to control the order of cell selection rather than having Excel control it, read on.
Fixing Macro Button Behavior in Protected Worksheets
When working with macro buttons, you may run into some bizarre behavior related to the macros without really understanding why. Here’s one explanation of what is going on and how you can fix it.
Functioning Check Boxes in a Protected Worksheet
Want to protect a worksheet but maintain the ability to make changes to the check boxes you add to the worksheet? Here is some information on how check boxes work, and how to update them within a protected worksheet.
Hiding and Protecting Columns
Want to hide certain columns within a worksheet so the contents are not visible to others? The answer lies in formatting the protection features in Excel.
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 All Non-Empty Cells
Need to make sure that your worksheet is locked, with only the blank cells accessible to editing? You can do this easily by using the Go To Special dialog box, or you can use a macro to do your configuration.
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 are some ideas on how to avoid this end-run on your work.
Protecting a Graphic
Need to make sure that someone cannot delete a graphic in a worksheet? The ability to protect the graphic depends on where you place it and how you protect the worksheet.
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 there, 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.
Excel allows data protection for particular cells or a whole worksheet in a shared work environment. Here's how to apply that protection.
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
It is not unusual, in a corporate world, to be handed a worksheet whose source you don’t know. If that worksheet is locked and you need to change it, then you become very interested in figuring out how to unlock the worksheet.
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 a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ways you can know for sure.