force paste comands to paste>special>values

Is it possible to execute (force) a paste special values command (by disabling or replacing paste commands with paste special values) when a user attempts to paste (via toolbar button, edit > paste, right click paste & ctrl-V) for the specific destination worksheet (specific active sheet).
To insure destination cell/s formatting and data validation are not replaced by that of the source.

All experts on the subject are welcome to assist.

Any ideas people ?
Thanks

Thnaks but ....
That does not prevent other users from pasting any data/formats to a cell via various paste techniques (via toolbar button, edit > paste, right click paste & ctrl-V)

It is these techniques that i want to force a paste aevent to a paste special values event

that is a solution to paste via ctrl-V only and does not prevent other methods of paste (via toolbar button, edit > paste, right click paste)
Any ideas for the others ?

This is the VB solution that seems to work for me. Hopefully it helps (first time I've offered a solution).

In each sheet you want to protect:

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

In ThisWorkbook:

Private Sub Workbook_Activate()
    Call ToggleCutCopyAndPaste(False)
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ToggleCutCopyAndPaste(True)
End Sub
 
Private Sub Workbook_Deactivate()
    Call ToggleCutCopyAndPaste(True)
End Sub
 
Private Sub Workbook_Open()
    Call ToggleCutCopyAndPaste(False)
End Sub

In module:

Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
        If cBar.Name <> "Clipboard" Then
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
        End If
    Next
End Sub
 
Sub CutCopyPasteDisabled()
     'Inform user that the functions have been disabled
    MsgBox "Sorry!  Cutting, copying and pasting have been disabled in this workbook!"
End Sub

Hi - I know that this is an old thread - but it very relevant to what i am trying to achieve.

I have used the code as instructed, but for some reason, when I try copy / paste from one workbook to another all paste and paste special commands are disabled.
The only time that Paste Special Values becomes enabled (and performs perfectly as I require) is when I
a) copy cells
b) close the workbook which I have copied from
c) ... and then try and paste in my target workbook (the one with the code) ... and then it works

Any ideas as to why? a remedy?

Thanks,

Originally Posted by jeffreybrown

Hi vofstein,

Yes this thread is old and it doesn't look like they are active right now.

To increase your queries exposure, please follow the forum rules and post your own thread. You can always place a link back to this thread if necessary.

OK- thanks Jeffrey - I will do so.