VBA: Change Sheet to VALUES

[SteveOranjin]

Hello,

I can't find anything online/in these forums about how to change the values of a sheet so it only accepts (and or changes) anything that is copied and pasted into the sheet into a "value".

I'd love to either set up a sheet so it:

• Automatically changes anything that is pasted into it into values only.
• Changes any value in sheet1 to a "value".

Hope you are well,

Steve

 

[mrbeanyuk]

Add this to your sheet's code. It forces paste special regardless of how you try and paste. There is no undo once run...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.CutCopyMode = xlCopy Then
 Application.EnableEvents = False
 Application.Undo
 Target.PasteSpecial Paste:=xlPasteValues
 Application.EnableEvents = True
End If
End Sub

 

[jim may]

Not sure, but here's a quick solution...

[TABLE]
<tbody>[TR]
[TD]Private  Sub Worksheet_Change(ByVal Target As Range)[/TD]
[/TR]
[TR]
[TD]'Erik Van Geit[/TD]
[/TR]
[TR]
[TD]'only allow PasteValues[/TD]
[/TR]
[TR]
[TD]If Application.CutCopyMode =  False Then Exit Sub[/TD]
[/TR]
[TR]
[TD]        With Application[/TD]
[/TR]
[TR]
[TD]        .ScreenUpdating = False[/TD]
[/TR]
[TR]
[TD]        .EnableEvents = False[/TD]
[/TR]
[TR]
[TD]        .Undo[/TD]
[/TR]
[TR]
[TD]        Target.PasteSpecial  Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/TD]
[/TR]
[TR]
[TD]        :=False, Transpose:=False[/TD]
[/TR]
[TR]
[TD]        .EnableEvents = True[/TD]
[/TR]
[TR]
[TD]        .ScreenUpdating = True[/TD]
[/TR]
[TR]
[TD]        End With[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]