VBA to disable Cut and Paste options within the Home ribbon AND the right-click menu?

Hello everyone! So I'm running into a few roadblocks trying to prevent people from pasting anything other than "values" only, as well as preventing people from using the Cut option. I have my worksheet locked, but multiple people are copying cells with various formatting (ie: cell background colors/fills), and the formatting is carrying over to my workbook and it is over-riding my conditional formatting. I've spent yesterday afternoon and this morning surfing the Internet for ways to prevent cutting and pasting, and so far I have found several macros out there, but none appear to stop any "work-arounds" for people that are trying to cut or use any other paste methods. At this point, I have successfully prevented the ability to do the CTRL + V and CTRL + X key combinations to prevent cutting and pasting that way, left-clicking a cell now automatically pastes the value only of anything on the clipboard, and it appears that the Cut method is now disabled when right-clicking on a cell with a value..... but I can still right-click a cell and choose any paste method I want, and the Cut and various Paste options are still available and functional from the Home ribbon.

I can't seem to find anything out there that successfully prevents the Cut and the Paste options from the right-click menu as well as the Home ribbon. Is anyone able to assist with this please? Training the various people using this spreadsheet to be sure to not use any other paste options is unfortunately out of the question, due to the over-whelming volume of people using my workbook.

Here is the code I'm currently using so far:

In a standard module:

Option Explicit
 
Sub ToggleCutAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, False) ' cut
    Call EnableMenuItem(22, False) ' paste
    Call EnableMenuItem(755, False) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = False
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^v", "CutPasteDisabled"
            .OnKey "^x", "CutPasteDisabled"
            .OnKey "+{DEL}", "CutPasteDisabled"
            .OnKey "^{INSERT}", "CutPasteDisabled"
        Case Is = True
            .OnKey "^v"
            .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
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=False)

    Next
End Sub
 
Sub CutPasteDisabled()
    MsgBox "Cutting and standard pasting have been disabled in this workbook."
End Sub

In "ThisWorkbook":

Sub Del_Item()
    CommandBars("Cell").Controls("Cut").Delete
    CommandBars("Cell").Controls("Paste").Delete
End Sub

Private Sub Workbook_Activate()
    Call ToggleCutAndPaste(False)
End Sub
 
Private Sub Workbook_Deactivate()
    Call ToggleCutAndPaste(True)
End Sub
 

Private Sub Workbook_Open()
   
    Call ToggleCutAndPaste(False)
    
    Application.CommandBars("Standard").FindControl(ID:=21).Enabled = False
    Application.CommandBars("Standard").FindControl(ID:=22).Enabled = False
    Application.CommandBars("Standard").FindControl(ID:=755).Enabled = False
    Application.CommandBars("Standard").FindControl(ID:=19).Enabled = False
    
    
    Application.CommandBars("Standard").Controls("Paste").Enabled = False
    Application.CommandBars("Edit").Enabled = False

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Call ToggleCutAndPaste(True)
 
End Sub

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

Any solid help would be really appreciated, as the various disabling of the "CommandBars" does not seem to be successfully disabling things everywhere. Thanks so much!

Hi Bernie, I appreciate the help! I applied your suggestion to a new workbook and see where it does offer some assistance with people left-clicking and right-clicking to paste clipboard data, and I think I may use that instead of a portion of code that I already have since mine didn't already prevent paste options when right-clicking, but any idea on how to completely prevent the option to Cut from the right-click menu and the Home ribbon, and how to prevent the option to Paste from the Home ribbon?? Thanks again!!

This is fantastic. I have made a small adjustment.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Select Case Application.CutCopyMode
        Case xlCopy
            If MsgBox("Paste here?", vbYesNo, "Paste Values Only") = vbYes Then
                Target.PasteSpecial xlPasteValues
            End If
        Case xlCut
            MsgBox "Sorry, Cut option is not allowed.  Try Copy instead.", , "Cut Not Allowed"
            Application.CutCopyMode = False
    End Select
End Sub

This allows multiple pastes of values from one copy and empties the clipboard if it's a cut.

Definitely understand being hesitant to alter the ribbons. The two reasons I'm wanting to do it though is because the above code doesn't prevent someone from cutting from my workbook and pasting in another workbook; and since there is a constantly-changing group of people from another division using this file it's going to be impossible to train them all, when I don't even know who the people are. So I basically merged your solution with this other one I just found this morning, and it seems to solve all of my problems...

ThisWorkbook:

Option Explicit
Private WithEvents Cmbrs As CommandBars


Private Sub Workbook_Open()
    Set Cmbrs = Application.CommandBars
End Sub


Private Sub Workbook_Activate()
    If Application.CutCopyMode = 2 Then
        Application.CutCopyMode = 0
    End If
End Sub


Private Sub Cmbrs_OnUpdate()
    If Me Is ActiveWorkbook Then
        If Application.CutCopyMode = 2 Then
            Application.CutCopyMode = 0
            MsgBox "Cut Operations disabled"
        End If
    End If
End Sub

Thanks again for the great help!!

Originally Posted by Bernie Deitrick

You could just add in the workbook_deactivate event and that will prevent them from cutting from yours into another.

Awesome! Thanks a ton, just did that and it did the trick!

Only other thing I'm hoping you can maybe help with.....

So I shot myself in the foot with this whole plan, for one small portion of VBA that I have that is commanded by a macro. I was assuming/hoping that since the pasting on this portion I'm speaking of was going to be done in VBA (versus from the user interface), that it would not be affected by the macro..... but I'm finding today that I was wrong.

Here is the snippet of code that is getting caught up by the macro of yours that I'm using on the module for Sheet5:

b.Cells(LRow + currRow - 1, "A").PasteSpecial xlPasteValues

I'm been struggling all morning to implement a way around it, but not being educated enough with how VBA within "ThisWorkbook" operates, my attempts at bypassing your VBA for my snippet above have failed. Is there any way you can think of that I can tell your VBA to not throw the MsgBox prompt when this other line of VBA in Sheet5 is called, and just allow it to paste the values like it normally would prior to me implementing your code?

Thanks so much once again!!!!!!!!!!!!!!

Bernie!! Thank you sooooo much!!! The first, simpler one worked like a charm! I really appreciate everything!!!