Protecting the formatting in Excel 2007

Question

I'm creating a number of forms in Excel 2007 for other people to use. They will be accessing the digital versions, and I'm afraid that they'll eventually accidentally break the formatting I've created. The most likely way they'll accidentally break it is by using copy/paste.

Is there any way to lock the formatting while still allowing the contents of cells to be modified? By formatting I mean:

• Cell Fill Color
• Borders
• Merged Cells
• Font type/height/formatting
• Contents of some cells (Cells that Label)
• Cell width/height (This is the least important, since it's unlikely they'll accidentially change this.

To be clear, I'm not trying to protect from malicious users. I'm trying to protect from well-meaning users who don't entirely understand excel and excel formatting. While I can teach them some basic tips on avoiding breaking things, in the end it's going to be necessary to have at least some basic format lock in place.

Solution 1

You could make a duplicate (optionally unformatted) sheet which is referenced by your formatted sheet. The formatting of the duplicate data entry sheet doesn't matter, it only provides the values. And you can fully protect the formatted (for printing etc.) version.

Solution 2

A combination of 2 settings provides you this functionality:

1. Select the cells where data entry will be performed, go in Format Cells, Protection and uncheck LOCKED.

2. On the Ribbon, under Review, PROTECT your worksheet. Notice that some exceptions are available: Select, Format cells, Insert, Delete, etc. Just leave them all unchecked, except Select. If your users need to insert rows, also include the exceptions to "Insert rows" (and perhaps "Delete rows").

Solution 3

• Under Review, unlock Protect, then go to to Home tab.
• under Font (bottom right corner) open Formatting, select the Protection tab and uncheck the Locked box.
• On the sheet, select the cells you would like to protect and go back to your formatting (under Font) and Protection and recheck the Locked box.
• Then go to your Review tab and select Protect sheet. Remember to select what actions someone will be able to change.

Comments

[Lance Roberts]

Hmm, I just went and tested .Protect UserInterfaceOnly:=True in my app also, and saw that you're right it doesn't protect the formatting of the unlocked cells. It just allows my VBA code to modify locked cells. I'll delete my answer.

[Move More Comments Link To Top]

Did you ever find a solution to this?

[JoshuaD]

No. Never found a suitable solution. :(

[JoshuaD]

This doesn't work. It locks the worksheet, but it doesn't allow the user to type in the boxes where they should be allowed to type. In essence, this turns it into a selectable picture. I need it to be a form where only the places where the user is supposed to enter information are editable and they can only edit the text, not any formatting.

[Lance Roberts]

@JoshuaD, you can probably uncheck most of those options except for Format Cells. I don't have Excel2007 handy to tell you which ones matter.

[mtone]

JoshuaD, did you do the first step I mentionned? If you did, you should have noticed that you can type in those specific cells where you removed the "Locked" checkmark.

[JoshuaD]

Hrm. That seems to be a very good start. It's not exactly what I was looking for (if you copy/paste formatting into one of the unlocked cells, the formatting is copied as well) but it's a very good start. Thank you.

[JoshuaD]

I tried this, but it doesn't seem to work. It protects the cell from being formatted, but it doesn't allow me to change the contents of the cell. I want borders/colors/size to stay the same no matter what, while allowing the user to type/paste/cut content

[Burgi]

Could you explain how the OP uses conditional formatting, perhaps with an example? Please take a look at How to Answer