CODEWITHSUNDEEP
excelexcel-2010vba
Ben A
Ben A

Reputation: 209

Restrict paste in Excel

I'm working on a protected workbook that only allows users to select and enter data into certain cells.
However, they have a propensity to paste data into these unlocked cells and I want to restrict this behavior in Excel 2010. I found some older VBA code but it seems that the ribbon makes most of it obsolete.

Upvotes: 1

Views: 2876

Answers (2)

Ben A
Ben A

Reputation: 209

I came across the following code that still works in Excel 2010.

'*** In a standard module ***
Option Explicit 

Sub ToggleCutCopyAndPaste(Allow As Boolean) 
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    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 "^c", "CutCopyPasteDisabled" 
            .OnKey "^v", "CutCopyPasteDisabled" 
            .OnKey "^x", "CutCopyPasteDisabled" 
            .OnKey "+{DEL}", "CutCopyPasteDisabled" 
            .OnKey "^{INSERT}", "CutCopyPasteDisabled" 
        Case Is = True 
            .OnKey "^c" 
            .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 
        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 

 '*** In the ThisWorkbook Module ***
Option Explicit 

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

The link to the info is: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

Upvotes: 0

SilentRevolution
SilentRevolution

Reputation: 1513

You may have to try something like this, I placed these subs in the workbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.CutCopyMode = False

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Application.CutCopyMode = False

End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Application.CutCopyMode = False

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.CutCopyMode = False

End Sub

Upvotes: 1

Related Questions