RESTRICT TO PASTE VALUES ONLY

[FARAZ QURESHI]

What would be the appropriate code to restrict the user from using pasting
format. In other words, whenever a user uses Ctrl+V only the values are
pasted.

Thanx in advance.

 

[Wigi]

Use Application.OnKey to assign one of your macro's to the shortcut
combination Ctrl-V.

Remember to set it back afterwards, or even if the user deactivates the
workbook (since this can be annoying).

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
.CutCopyMode = False
End With
End Sub

Note: works only with with copy/paste, not cut/paste


Gord Dibben MS Excel MVP

 

[aggiemullins]

Is there one for copy/paste?

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is copied over
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
.CutCopyMode = False
End With
End Sub

Note: works only with with copy/paste, not cut/paste


Gord Dibben MS Excel MVP

 

[Gord Dibben]

I think you mean cut/paste.

No. I don't have code for that.


Gord