PDA

View Full Version : [SOLVED] Solved: force pastespecial values



BrianMH
02-06-2013, 10:04 AM
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
02-07-2013, 08:54 AM
perhaps...

http://www.mrexcel.com/forum/excel-questions/56674-force-paste-special-values.html

mancubus
02-07-2013, 08:55 AM
http://www.mrexcel.com/forum/excel-questions/56674-force-paste-special-values.html#post263183


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

BrianMH
02-08-2013, 03:28 AM
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.

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

snb
02-08-2013, 05:50 AM
or ?

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

abline
01-28-2014, 06:05 AM
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
01-28-2014, 08:53 AM
@abline,

Can you use code tags, please ?