2

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.

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

5 Answers 5

Reset to default
1

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

Improve this answer
6
  • 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
    Commented Jan 14, 2010 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
    Commented Jan 15, 2010 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
    Commented Jan 15, 2010 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
    Commented Jan 15, 2010 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
    Commented Jan 15, 2010 at 18:03
1
  • 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.
Improve this answer
1
  • 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
    Commented Sep 16, 2011 at 14:26
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.

Improve this answer
0

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

Improve this answer
0

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

Improve this answer
1
  • Could you explain how the OP uses conditional formatting, perhaps with an example? Please take a look at How to Answer
    – Burgi
    Commented May 6, 2016 at 10:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .