Restrict User to Paste Value

[smstrickland]

I have been reading past posts to find an answer to my dilema, but have yet to find one I believe applies. I have cells with conditional formatting (no drop downs) and I want to ensure that if a user pastes data into the cells, they can only past values. I.e., it is okay for them to paste data in, but I want to retain the data validation on the cells. Any thoughts?

 

[GWteB]

Welcome to MrExcel!
This code might work. Paste it in the module of your worksheet: right click on Worksheet tab > View Code (VBE opens) > paste code > close VBE.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim raSel       As Range
    Dim vFormula    As Variant

    Set raSel = Selection
    vFormula = Target.Formula
    With Application
        .EnableEvents = False
        .Undo
        Target.Formula = vFormula
        raSel.Select
        .EnableEvents = True
    End With
End Sub

 

[smstrickland]

Thank you for your reply. This does seem to work with regards to the original question; however, the undo/redo button is no longer visible. Any thoughts on how to handle this?