Changing all "Paste" commands to "Paste" Value or Text only

[pepps]

I have a spreadsheet that is used as a form. The spreadsheet is locked, but users can still "paste" data into cells. This works fine, except that unless the user does a "Paste Special", "Value" or "Text", the Paste brings color and line formatting with it. Does anyone know how to make all "Paste" command default to pasting the value or text only?

[wallyeye]

You could use the worksheet_change event to reset the formatting to your default. I assume you have the entry cells formatted the same, it would just be a matter of duplicating that. Or, just copy/pastespecial the format from a designated protected cell:

Please Login or Register  to view this content.

Where range A1 has the format you want to reset to.

[pepps]

Thanks for the approach. In my case, the source data could come from almost anywhere and the destination cells include many different formats. Some are single cells, while others are a column with 60+ rows. If you paste manually, and select "text" or "value" they all work fine, but if you don't, users can overlay the format (color, lines, etc.)

Any other or modified thoughts?

Thanks,

Paul

[wallyeye]

Should have thought of this the first time...

Please Login or Register  to view this content.

[pepps]

I'll try it, but would you mind explaining it a little? Does this intercept the Paste function (for the current tab or the entire workbook?) and modify it with a Target.Value (paste special.Value)? Not sure what the Application.Undo does?

[wallyeye]

This saves the updated value (any update, not just pasted ones) into varvalue, undos the last action (typing or pasting), then puts only the value back into the updated cell. It won't work well if multiple cells are updated at the same time, but could easily be tweaked to work.

[pepps]

I've tried the suggested code, but when I cut and paste a cell that has color or a specific format (such as date) it still applies the color and/or format instead of just the data. Any other thoughts?

[wallyeye]

Is it actually triggering? That is, if you put a breakpoint on the varvalue = target.value line (select the line, press F9), then paste a value on the worksheet, does it stop at the breakpoint?

You might need to open the immediate window (ctrl-G), and run this:

application.enableevents = true

(just paste it in and hit the enter key with the line selected).

Or, after you've pasted in the worksheet_change event, save the workbook as a macro-enabled workbook, close it, and open it again.