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.

  • 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. – Lance Roberts Jan 15 '10 at 0:12
  • Did you ever find a solution to this? – Move More Comments Link To Top May 26 '15 at 13:06
  • No. Never found a suitable solution. :( – JoshuaD May 26 '15 at 21:41

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").

  • 1
    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. – JoshuaD Jan 14 '10 at 23:37
  • @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. – Lance Roberts Jan 15 '10 at 0:17
  • 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. – mtone Jan 15 '10 at 3:41
  • 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 Jan 15 '10 at 16:25
  • I agree, I think you will need to figure a VBA macro to catch the "change" event to prevent anything but text. At least with the worksheet locked you have less things to worry about in your macro. – mtone Jan 15 '10 at 18:03
  • 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.
  • 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 – JoshuaD Sep 16 '11 at 14:26

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.


Click Developer tab, Visual Basic button, to open VB code screen. Paste the following:

Dim cellcolor As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
  Target.Interior.Color = cellcolor
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   cellcolor = Target.Interior.Color
End Sub

This defines a global variable to catch the color of the cell you selected to paste into (in the SelectionChange event). Then, when you Paste, it goes to the Change event and sets the cell to the color you caught earlier.

Caveat:The VB code works, but then it won't allow an Undo. (You Copy/Paste and it fixes the cell color back to what you had, but if you decide you didn't want to paste, well, too bad. Maybe someone can assist there.)

Works great for that, but: Doesn't catch intentional format changes. (select cell; click Fill Color).


Use conditional formatting. As long as you identify the range and formatting options you want, it'll keep all formatting.

  • Could you explain how the OP uses conditional formatting, perhaps with an example? Please take a look at How to Answer – Burgi May 6 '16 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.