Disabling paste options except paste values in a shared excel file

**Alex.Mac** · 11-29-2014, 03:16 AM

Hi guys,

I am not sure if I should post different problems in different threads or should I post all problems in one thread, so I will post for now 1 problema /thread

.

I am working with a shared document on which multiple users are performing modifications in the same time. The problem is that some of them have a below zero excel aptitude level and they are copy/pasting a lot, erasing cell's formatting and dta validation criteria.
I need a method to prevent that, to lock cell format or to disable all copy/paste options except paste special - values.

I have read some other discussion with this topic over the web, but none of them offered a valid solution.

Thank you !

**ELeGault** · 11-29-2014, 03:39 AM

Can you share a sample file... For format issues you may be able to utilize conditional formatting (CF) by adding identifiers around the table they are working in.
Another option is to hold an excel class... Not sure what that would take for you so -
There are probably solid solutions here, but a sample book would help in finding the right solution for you

**Alex.Mac** · 11-29-2014, 12:26 PM

Well, I didn't post an example because I have a wide range of format options that I want to protect. The bottom line is that I want that any copy/paste action to behave as a paste special-values, so that nobody, except me, would be able to copy/paste formulas or formatting (borders, pattern, font, colors, data validation-lists, etc.). Things like borders, pattern, colors, cell category I can manage with conditional formatting, but I can'prevent somebody from pasting a number cell over a data validation cell which should be a value chosen from a list. I hope I explained the situation clearly..

**ELeGault** · 11-29-2014, 01:14 PM

you could lock down the sheet -
Select all and go to Format Cell options Ctrl+1, on the Protection tab, uncheck the Lock.
Now select all the cells that are formulas or drop down menus. Choose to Lock them.
Now Right click the sheet tab you are doing this on and Protect it. Allow them to select locked cells, they just wont be able to edit them. (So no copy paste)
You of course will have to unlock it to edit it yourself.
As for format... I am not sure if there is a simple way, but what I would do if I had a wide range of them is make a duplicate sheet and hide it.
When they have messed up visuals you could go copy that sheet, comes back to the main sheet and pasts special Formats. (you could make a macro to do this as well) -

Final option would be, lock it all down and make a mirror sheet that they work in and you only take the information from the fields they you care about into your sheet that is formatted.

Not sure if that helps - hard to give full suggestions without knowing your layout within that sheet