Force paste values

[yrpsoa]

I'm trying to force users to paste as values to preserve conditional formatting in the workbook.

The following works for numbers but can someone please assist me in how to modify the code for pasting unformatted text (ie. from the internet) - at the moment this is returning an error because i have overriden ctrl + v. I'm thinking an if statement but not very familiar with the syntax of VBA to implement this.

I would also like to change it do dismiss the error message when there is nothing on the clipboard: Run-time error '1004': PasteSpecial method of Range Class failed.

Thanks in advance for any help.

Sub paste_as_values()'
' paste_as_values Macro
' Paste as values to retain conditional formatting. Do not override.
'
' Keyboard Shortcut: Ctrl+v
'
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Yongle]

try putting this in the sheet module (right-click on tab \ View Code \ paste into code window)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x, rng As Range, last As String
    On Error Resume Next
    Set rng = Target.SpecialCells(xlCellTypeFormulas)
    last = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Not Intersect(rng, Target) Is Nothing Then
        If Left(last, 5) = "Paste" Or last = "Auto Fill" Then
             x = Target.Value
             Application.Undo
             Target.Value = x
        End If
    End If
End Sub

Let me know if it fits your requirements

 

[yrpsoa]

Hi Yongle

Thanks for this. It is almost perfect bar a few tweaks:
- It seems to evaluate the formula based on the destination cell and then convert it to a value rather than retaining the original value.
- Pasted text retains the source formatting.
- It removes the ability to undo

The paste > undo > paste values method seems to be causing a few problems.

Is there no way to meet in the middle and assign ctrl + v to a function which does paste special > unformatted text if the clipboard content is text, otherwise paste special > values?

Thanks again for your help.

 

[Yongle]

Probably little different but try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
    With Application
        If .CommandBars("Standard").Controls("&Undo").List(1) = "Paste" Then
            .EnableEvents = False
            .Undo
            Target.PasteSpecial (xlPasteValues)
            .EnableEvents = True
        End If
    End With
End Sub

 

[Yongle]

Perhaps this is better but I have not considered what might cause the code to error

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As String
    With Application
        If .CommandBars("Standard").Controls("&Undo").List(1) = "Paste" Then
            .EnableEvents = False
            t = Target.Text
            .Undo
            Target = t
            .EnableEvents = True
        End If
    End With
End Sub

 

[yrpsoa]

The second one doesn't appear to be running as ctrl + v performs a paste all and the undo history is preserved.

I had more success with the first one - works perfectly within the sheet... but has no effect when pasting from different sheet/workbook...

Any thoughts on this?

I've also added the On Error Resume Next to cover images/text pasting.

Thanks