Excel 2007 : Paste Value

[pepps]

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. 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?

[FDibbins]

use data validation to restrict entry in those cells to values only

[pepps]

I have data validation for my 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. Any other thoughts?

[FDibbins]

when you set sheet protection, did you uncheck "format cells"? check under there and uncheck whatever you need the user to not have access too?

[pepps]

Yes, 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.

[FDibbins]

hmm ok, got me stumped, sorry

bump?

[pepps]

Anyone have any thoughts on a approach?

[Mordred]

Perhaps:

Please Login or Register  to view this content.

Edit: Sorry I didn't read more about the colour backgrounds but I'm sure that that can be worked into the worksheet change event.

[tkowal]

A thought came to mind, sort of a work around :

What if in the change event, you trap cells of concern... copy the target's value.... issue an undo and re-assign the value....

perhaps something along these lines: (Note: this is not tested)

Please Login or Register  to view this content.

After a quick test on a sheet with cell formats, backgrounds and borders... it seemed to work...