+ Reply to Thread
Results 1 to 2 of 2

Removing a user's ability to paste

Hybrid View

  1. #1
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Removing a user's ability to paste

    I have several worksheets in my workbook in which I do not want a user to have the ability to copy/paste, or more importantly paste. If they cannot paste, then the copy function is moot (and vice versa), so really I just need to remove their ability to do one or the other. Is there VBA that can be inserted into the worksheet module to accomplish this?

    Update: Or just remove their ability to "right-click", and disable "Ctrl-C". The ribbon is otherwise gone, so these two things might also accomplish what I am setting out to do.
    Last edited by jonvanwyk; 03-08-2012 at 10:50 AM.

  2. #2
    Forum Contributor jonvanwyk's Avatar
    Join Date
    06-28-2010
    Location
    Iowa, USA
    MS-Off Ver
    Excel 2010
    Posts
    452

    Re: Removing a user's ability to paste

    Nevermind. I figured it out, and the solution is listed below.

    Just use this code 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
    ...and then this code 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1