Preventing pasting except for Paste Values

[Bob]

Within this Discussion Group, I have searched for "restrict pasting" and
"prevent pasting", but I have been unable to find a thread that solves my
problem.

I want to prevent users from pasting anything in a cell except for values.
So when a user copies some data and attempts to paste it in a cell using Edit
| Paste, Ctrl-v, the Paste button, or the right-click menu and selecting
Paste, I want the paste operation to behave as if the user had selected Paste
Special | Values.

Can this be done programmatically? If so, and being a novice to VBA, I
would be grateful for any assistance.

Thanks,
Bob

 

[Rick Rothstein]

I'm not sure I understand what it is you are trying to restrict. Paste
Special Values takes the output of a copied cell (or cells), normally with a
formula in it (them) and pastes the evaluated value (number or text) into a
different cell (or cells), but it also copies raw data (either numbers or
text) and pastes them as well. Exactly what it is you want to allow to be
pasted or, put another way, what is it you are trying to restrict from being
pasted?

 

[Rick Rothstein]

What about a macro that simply removes any leading or trailing spaces
instead...

Sub RemoveLeadingTrailingBlanks()
Dim X As Long, Z As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To 5
Cells(X, Z).Value = Trim(Cells(X, Z).Value)
Next
Next
End Sub

 

[Rick Rothstein]

Ignore this last message (the one with the RemoveLeadingTrailingBlanks macro
in it)... this was meant for another thread. My questions posed in my first
message still need to be answered.

 

[Bob]

Rick,
Forgive me for not being more clear in describing the restrictions. I want
to prevent users from overwriting the cell formats (including conditional
formatting) and validation rules I have established. Hence, my need to
restrict users to just pasting only "values".

Bob

 

[Satish]

Rick,
Forgive me for not being more clear in describing the restrictions.  I want
to prevent users from overwriting the cell formats (including conditional
formatting) and validation rules I have established.  Hence, my need to
restrict users to just pasting only "values".

Bob





- Show quoted text -

Hi Bob,

Instead of changing the way Excel behaves during paste, I would rather
not allow the user to change the data (Disable the copy-paste). I
would use a macro on some button and provide the paste feature. I am
not sure if it works for you, but you may want to consider this
option. Here is the code that you need to put on any button -

'this will copy only values from the range B2 to B6 and paste them
from C2 to C6.
Sub PasteOnlyValues()

With Worksheets(1)
.Range("B2:B6").Copy
.Range("C2").Select

Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
skipblanks:=False, _
Transpose:=False
End With

Application.CutCopyMode = False

End Sub

HTH,
Regards,
Satish

 

[Patrick Molloy]

use the worksheet change event might do this

right click the sheet tab and select View Code

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.CutCopyMode Then
Target.PasteSpecial xlPasteValues
End If

End Sub

 

[Bob]

Satish,
Thanks for your suggestion, but I don't want to disable the ability for a
user to paste. I just want to restrict pasting to pasting only values.

The users typically copy data from MS-Word documents into my worksheet. And
they typically (but not always) use the Ctrl-v keyboard shortcut. However,
when they do this, they wipe out all my conditional formatting and validation
rules. That's what I'm trying to protect. Hence, my request for a solution
that will allow users to paste data in the cells, but only "values".

Bob

 

[Bob]

Patrick,
Thanks for your suggestion, but please see my response above to Satish's
suggestion.

Bob