I have a spreadsheet full of validations and different
formatting. I want to protect it all but at the same
time, let the user change the values in all the cells.
Everything seems to work except with it comes down to
copy and pasting. If I copy from a certain cell with a
certain format and then paste it in a different cell with
a DIFFERENT format, it somehow overides the format.

Cell 1 has numbers
Cell 2 accepts only letters

Copy cell 1 into cell 2 and it works yet if you type it
manually without copy pasting there is a pop up with an
error.

Any ideas on how I can protect the formatting using vba
so even the "copy & paste" won't get rid of it?!

Thanks,

Marc

Hi Macr,

I have the following suggestions for you:

Solution one: Reformat it every time when user does something with the cells
Have one macro to record the last selected cell(s) and have the second macro (Worksheet_SelectionChange) to refresh it. The second macro reformats it based on the location of the last selected cell.

Solution two: Prevent user from paste anything onto the worksheet
In Worksheet_Activate, Workbook_WindowActivate, and Worksheet_SelectionChange macros add this line: "Application.cutcopymode=false"


"Marc" wrote:

I have a spreadsheet full of validations and different
formatting. I want to protect it all but at the same
time, let the user change the values in all the cells.
Everything seems to work except with it comes down to
copy and pasting. If I copy from a certain cell with a
certain format and then paste it in a different cell with
a DIFFERENT format, it somehow overides the format.

Cell 1 has numbers
Cell 2 accepts only letters

Copy cell 1 into cell 2 and it works yet if you type it
manually without copy pasting there is a pop up with an
error.

Any ideas on how I can protect the formatting using vba
so even the "copy & paste" won't get rid of it?!

Thanks,

Marc