I have a spreadsheet that is used to collect information, ie, a form with data entry fields. I lock all the fields that should not change, and protect the worksheet. I have data validation for numeric fields as "whole number, greater than or equal to 0". However, if the user pastes a green background with .8 thick lines, it will paste numeric only data, but mess up the color and border formatting. The only thing the user can do is "Select locked cells" or "Select unlocked cells". The Select locked cells is checked because I use the "Comments" as mouse-over pop-up instructions.

This works great except that I can't stop people from pasting into the fields that are not locked. I would be fine if they pasted/value only. Does anyone know a way in VB to ensure that ALL pastes to a worksheet automatically and only use Paste/Value feature?

hi pepps,

the only solution i see is to apply the format known to cells that undergo a change.
this is possible via the event:
Private Sub Worksheet_Change (ByVal Target As Range)

--
isabelle



Le 2012-06-08 18:02, pepps a écrit :
I have a spreadsheet that is used to collect information, ie, a form
with data entry fields. I lock all the fields that should not change,
and protect the worksheet. I have data validation for numeric fields as
"whole number, greater than or equal to 0". However, if the user pastes
a green background with .8 thick lines, it will paste numeric only data,
but mess up the color and border formatting. The only thing the user
can do is "Select locked cells" or "Select unlocked cells". The Select
locked cells is checked because I use the "Comments" as mouse-over
pop-up instructions.

This works great except that I can't stop people from pasting into the
fields that are not locked. I would be fine if they pasted/value only.
Does anyone know a way in VB to ensure that ALL pastes to a worksheet
automatically and only use Paste/Value feature?