Unlocked Cells on Protected Sheet Still Allowing Formatting Changes

[bonzo]

I'm using Excel 2007.

I have formatted a handful of cells on a sheet such that those cells have the "Locked" attribute *unchecked*. The intent is for users of the sheet to *only* update the contents of the *unlocked* cells. The rest of the cells in the sheet all have their "Locked" attribute *checked*.

When I select "Protect Sheet", I only *check* the following attributes:

Select Locked Cells
Select Unlocked Cells

All other attributes are *unchecked* (including "Format Cells" expecting no formatting changes to be allowed). After protecting the sheet, someone takes a cell from another location (source cell), does a Ctrl-C to copy the cell, and then does a Ctrl-V on one of the *unlocked* cells (target cell). That unlocked target cell's format is changed to match the format of the source cell.

Have I wrongly assumed that because the "Format Cells" attribute is *unchecked* that the cell's format should not change and that only the cell's contents would be copied?

The whole purpose of protecting the sheet was to prevent any changes to the sheet except for the contents of the *unlocked* cells. Am I missing something?

Hopefully, there's a solution to this dilemma.

Thanks...

 

[philrv]

what you could do is intercept or disable right-click menus to prevent users from copying or pasteing. search forum for vba code.

 

[BrandonWLH]

Unlocked cells can be altered. They are unlocked.

If you don't want someone to copy and paste a format, make sure you lock all formatted cells and untick Select Locked Cells. If a cell is locked, there is typically no reason for someone to click/select it unless you are trying to capture the mouse clicks.

That way, you leave all the unlocked cells the plain formatting so they are not copying any formatting.

You could also lock the whole sheet and use a userform to populate the data into the sheet.