Solved: force pastespecial values

[BrianMH]

Hi,

Does anyone know of any code that will force pasting as values on a worksheet? There may be code out there but due to some restrictions on viewing forums at work (sure glad I can still get here) I can't view them.

I want to force a paste to be pastespecial values for a worksheet no matter where the copy was done from and whether they use control + v, the edit menu or the context menu.

Any ideas?

[mancubus]

perhaps...

http://www.mrexcel.com/forum/excel-q...al-values.html

[mancubus]

http://www.mrexcel.com/forum/excel-q...tml#post263183

[VBA]
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
[/VBA]

[BrianMH]

Thanks for that. I actually ended up going another way. I named a range that I wanted to protect and then used that in a script to capture the values that were pasted or changed and then enter them as values. I also wanted to protect any formulas in the range so added something to do that. I figured I would post it here as it may be useful to others. I actually think this would resolve peoples issues with validation being overwritten too.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValue As Variant
If Not Intersect(Target, Me.Range("GIFormatted")) Is Nothing Then
Application.EnableEvents = False
vValue = Target.Formula
Application.Undo
If Target.HasFormula Then
If Not Target.Formula = vValue Then
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have deleted an important formula."
GoTo exiter
End If
End If
Target.value = vValue

End If

exiter:
Application.EnableEvents = True
Exit Sub
errorhandler:
Application.EnableEvents = True
MsgBox Err.Number & " - " & Err.Description
End Sub
[/VBA]

[snb]

or ?

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("GIFormatted")) Is Nothing Then
on error resume next
Application.EnableEvents = False
for each cl in target.specialcells(-4123)
cl.value=cl.value
next
application.enableevents=true
end if
End Sub[/vba]

[abline]

This will give you pastespecial instead of paste:

Private Sub Worksheet_Change(ByVal target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Dim vCopyValue As Variant
Dim vCopyFormula As Variant
Dim CellAddr As String
CellAddr = ActiveCell.Address
On Error GoTo ExitClean
vCopyValue = target.Value
vCopyFormula = target.Formula
Application.Undo
target.Value = vCopyValue
target.Formula = vCopyFormula
On Error GoTo 0
ExitClean:
Application.CalculateFull
Range(CellAddr).Select
ActiveSheet.Protect Contents:=True, AllowFormattingRows:=True, AllowFormattingCells:=True, AllowInsertingHyperlinks:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[snb]

@abline,

Can you use code tags, please ?