I have a worksheet containing a column of numbers. The column is formatted with a background color, number formats, etc. The column is unlocked. I protect the sheet manually by right-clicking on the tab and selecting Protect. In the Protect Sheet dialog, 'format cells' is unchecked. I interpret to mean that the user should not be able to format cells. Yet when the user pastes into the column, formats are pasted along with values.

If I protect the sheet in VBA using sh.Protect UserInterfaceOnly:=True I get the same result: formats are pasted. I do not specify AllowFormattingCells:=False because the default is False.

I have seen posts suggesting that formats can be restored by copying and pasting them from a shadow area. I have used this solution before I started protecting worksheets and found it overly complex. I had hoped this was something protection would handle. If there is a way to handle it, I'd like to do it in VBA.

CodePudding user response：

There is no built-in protection option you can use to achieve your desired result.

The only thing that works in this case is the clunky workaround that you mention, i.e. use a Worksheet_Change event that ensures the correct format after a cell has been modified.

Since there are many different ways to paste content, i.e. via various menus, ribbon commands, keyboard shortcuts, etc., any VBA solution that tries to intercept pasting will become very complex, much more complex than the change event that restores the format to its original state.

Another option might be user education and training (so they know to paste values only), although user behaviour may be the toughest element to change in the whole scenario.