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
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
@abline,
Can you use code tags, please ?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.