when paste, paste values only

[zico8]

Hi,

I would like to set my sheet that only copying values is allowed.

I know I should use

.PasteSpecial xlPasteValues

, but how should I use it if I want to paste values only when user will try to paste something into cell?

 

[Yongle]

try this

goes in SHEET module (right-click on sheet tab \ View Code \ paste in code window)

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

 

[Yongle]

Please ignore my previous effort
- try this instead

goes in SHEET module (right-click on sheet tab \ View Code \ paste in 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

 

[Yongle]

Also consider this method which disables Copy&Paste at WORKBOOK level
If it appeals, you would need a separate macro allowing the user to Copy&PasteValues.