How to Avoid Formatting Cells When Protected?

[aerodoc]

I have cells that are not protected, but then I protect the sheet with only the "Select unlocked cells" option checked. Since there is a "format cells" box that is not checked, it implies I should not be able to format the cells. I can not directly format cells this way.

But the problem is that if I do a drag (with plus sign) or a copy/cut/paste it modifies the formatting. For example, I have an outside border on a group of cells. If I drag the first cell down, all of the cells have borders. This is not my intent and I have no way to stop this from happening. Any ideas?

[p45cal]

Would Ctrl+C then Paste-Special|Formulas do it for you?

[DILIPandey]

Hi Aerodoc,

I followed like:-
First I unlocked all the cells
I selected a group of cells and locked them
Now, I protected the sheet with the option "Select Unlocked cells"
and now I can not do modification on that group of cells and this is what you needed .. correct ?

Where is the gap ?


Regards,
DILIPandey

[aerodoc]

See first post:

"But the problem is that if I do a drag (with plus sign) or a copy/cut/paste it modifies the formatting. For example, I have an outside border on a group of cells. If I drag the first cell down, all of the cells have borders."

[DILIPandey]

You said in your first post that
I have cells that are not protected, , and after that you protect the sheet.

Now if these cells are not protected then they are out from the locking / protecting the sheet and you can do anything to that group of cells.

Regards,
DILIPandey

[Paul_Hossler]

But the problem is that if I do a drag (with plus sign) or a copy/cut/paste it modifies the formatting. For example, I have an outside border on a group of cells. If I drag the first cell down, all of the cells have borders. This is not my intent and I have no way to stop this from happening. Any ideas?



A 'normal' Copy applies the formatting. Copy, PasteSpecial Values will only update the 'numbers'

Control-C or Drag or Fill is a normal copy

Paul

[aerodoc]

From first post:

"I have cells that are not protected, but then I protect the sheet with only the "Select unlocked cells" option checked. Since there is a "format cells" box that is not checked, it implies I should not be able to format the cells. I can not directly format cells this way."

There is a distinction. The "select unlocked cells" means you can only select them (sort of). If you try to directly apply formatting (try to right click and you see things are greyed out) you can not. Try to use the fill drop down menu and it is greyed out. So this sort of stops you from formatting the cell. It is only under cut/paste or drag options that the formatting gets adjusted. So you can't just do "anything to them". You would have to change the options in the spreadsheet protect to set that.

A paste special will work, but the problem is that this is for a general user and they usually don't want to mess with that (or immediately realize it is necessary).

The workaround I used was to use VBA to reformat things to their original state with a repaint button.

[DILIPandey]

Sorry.. I am confused.


I have cells that are not protected how you ensure this ? Thanks.


Regards,
DILIPandey