VBA - Restrict Users To Paste As Values Only

[Lewzerrrr]

Hey,

I've been looking and haven't found a clear resolution to this but I'm sure it's possible.

I have a couple of sheets that my colleagues need to input into but they all use ctrl+v which overrides the formatting, to save time in tidying it up after, is it possible to add a code through a worksheet_change that DISABLES all pastespecials apart from values only?

Thanks,

 

[XLfriend777]

Hi Lewzerrr - You might consider something like the code below that takes whatever has already been selected and then pastes the values into the active cell.

Sub Lewzerrrr_PasteSpecial()
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[bsquad]

I can't remember where I found this - but it definitely wasn't me credit wise. I use it in one of my sheets as well

The following has to be pasted in ThisWorkbook and not a separate Module

 Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Target.PasteSpecial xlPasteValues
    Application.CutCopyMode = True
End Sub

 

[Jaafar Tribak]

Why don't you hook CTRL+V for those specific sheets and associate to it PasteSpecial xlPasteValues