Disable Paste(?)

[doofusboy]

Is it possible to prevent a user from Pasting to any cell in a spreadsheet?

[M Owen]

Doof,

Only thing I can think of is to block/capture the Crtl - V sequence ...

[doofusboy]

Thought of that, but what about pasting from toolbar, or drag-drop?

[Chien9999]

Should you protect the speadsheet incase you only want to view the spreadsheet ?

[doofusboy]

Thanks Chien, but no. I need a slew of users to input data to the spreadsheet and I'm trying to control what they put in there.

I've put Data Validation on all the cells but it gets blown away if someone pastes data into a cell.

[WorkHorse]

I don't know of any way to stop paste on the basic process level. I wish MS would put all command processes into workbook events. But I came up with this, which should disable any paste functions in Excel. I can't guarnatee that I caught them all, but this should give pretty good protection.

In a module:

Option Explicit
 
Sub PasteEnable(ByVal blnEnabled As Boolean)
    
    On Error Resume Next
 
    ' Insert command controls.
    CommandControlEnable 248, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 262, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 272, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 282, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 295, blnEnabled, True  ' Copied C&ells...
    CommandControlEnable 299, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 316, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 340, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 454, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 468, blnEnabled, True  ' Insert Copied C&ells...
    CommandControlEnable 3184, blnEnabled, True ' Copied C&ells...
    CommandControlEnable 3185, blnEnabled, True ' Insert Copied C&ells...
    CommandControlEnable 3187, blnEnabled, True ' Insert Copied C&ells
    
    ' Paste command controls.
    CommandControlEnable 22, blnEnabled, True   ' &Paste
    CommandControlEnable 755, blnEnabled, True  ' Paste &Special...
    CommandControlEnable 879, blnEnabled, True  ' &Paste...
    CommandControlEnable 945, blnEnabled, True  ' &Paste
    CommandControlEnable 2787, blnEnabled, True ' Paste as &Hyperlink
    
    ' Paste All on Clipboard toolbar cannot be disabled.
    ' Disable/enable toolbar completely.
    'CommandControlEnable 3633, blnEnabled, "Paste A&ll", "Paste A&ll Disabled"
    CommandBars("Clipboard").Enabled = blnEnabled
   
    ' Set protection from customization for all toolbars.
    CommandBarsProtectionEnable blnEnabled
   
    ' Paste keyboard functions.
    If blnEnabled = True Then
        Application.OnKey "^v"
        Application.OnKey "+{INSERT}"
    Else
        Application.OnKey "^v", "PasteEnableFalseMessage"
        Application.OnKey "+{INSERT}", "PasteEnableFalseMessage"
    End If
 
End Sub
 
Sub CommandControlEnable(ByVal lngCommandId As Long, _
                         ByVal blnEnabled As Boolean, _
                Optional ByVal blnChangeCaption As Boolean)
 
    On Error Resume Next
 
    Dim ctlControls As CommandBarControls
    Dim ctlControl  As CommandBarControl
    Dim strCaption  As String
    
    ' Get all matching command controls.
    Set ctlControls = CommandBars.FindControls(ID:=lngCommandId)
    If Not ctlControls Is Nothing Then
        
        ' Get caption.
        strCaption = ctlControls.Item(1).Caption
        If blnChangeCaption = True Then
            If blnEnabled = True Then
                If Right$(strCaption, 9) = " Disabled" Then
                    strCaption = Left$(strCaption, Len(strCaption) - 9)
                End If
            Else
                If Not Right$(strCaption, 9) = " Disabled" Then
                    strCaption = strCaption & " Disabled"
                End If
            End If
        End If
        
        ' Toggle enabled and set caption.
        For Each ctlControl In ctlControls
            ctlControl.Enabled = blnEnabled
            ctlControl.Caption = strCaption
        Next
    
    End If
 
End Sub
 
Sub CommandBarsProtectionEnable(ByVal blnProtect As Boolean)
 
    On Error Resume Next
 
    Dim intProtect  As Integer
    Dim i           As Integer
 
    ' Get protection value.
    If blnProtect = True Then
        intProtect = msoBarNoProtection
    Else
        intProtect = msoBarNoCustomize
    End If
 
    ' Set protection for all command bars.
    For i = 1 To CommandBars.Count
        CommandBars(i).Protection = intProtect
    Next
 
End Sub
 
Sub PasteEnableFalseMessage()
    ' Display message for Paste disabled.
    MsgBox "Paste Disabled" & vbCrLf & vbCrLf & _
           "Paste function is not allowed in this workbook.", _
           vbExclamation
End Sub

In a workbook:

Option Explicit
 
Private Sub Workbook_Open()
    ' Disable Paste method.
    PasteEnable False
End Sub
 
Private Sub Workbook_Activate()
    ' Disable Paste method.
    PasteEnable False
End Sub
 
Private Sub Workbook_Deactivate()
    ' Enable Paste method.
    PasteEnable True
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Enable Paste method.
    PasteEnable True
End Sub

[doofusboy]

Yo WorkHorse, thanks man...........exactly what I needed!

[doofusboy]

Ooops, perhaps I spoke too soon?


WorkHorse, your code seems to cover everything but the Paste button on the toolbar in Excel. Is it possible to disable this button?

[Ephesians]

Should be possible using apis...

do a search on disabling menu commands in the api section.

[WorkHorse]

Which Paste button on what toolbar? The CommandControlEnable function disables the control on every toolbar in the activeworkbook. The Paste button on the Excel Standard toolbar should be control 22. That one is on the list. It should get every Paste button and menu option that I could find. What version of Excel are you runing? (you don't need no APIs. )

P.S.: This only disables Paste in the Workbook that has the code. All the other Workbooks should still allow Paste.

[doofusboy]

Sorry for the delay in responding. Was referring to the Paste button on the standard Excel toolbar and I am using Excel 2002.

[byyu]

I found the solution! I know this is an old post, but for those people who are reading this in the future.

Took me a while to find the specific ID numbers, but I found the ones for the paste button in the standard toolbar.

Use this code for the 'Paste command controls. section instead.

' Paste command controls.
CommandControlEnable 22, blnEnabled, True   ' &Paste
CommandControlEnable 370, blnEnabled, True  ' &Values
CommandControlEnable 385, blnEnabled, True  ' PasteFunction
CommandControlEnable 755, blnEnabled, True  ' Paste &Special...
CommandControlEnable 879, blnEnabled, True  ' &Paste...
CommandControlEnable 945, blnEnabled, True  ' &Paste
CommandControlEnable 1956, blnEnabled, True ' Paste Li&nk
CommandControlEnable 2787, blnEnabled, True ' Paste as &Hyperlink
CommandControlEnable 5836, blnEnabled, True ' &Formulas
CommandControlEnable 5837, blnEnabled, True ' No &BordersPaste
CommandControlEnable 5838, blnEnabled, True ' &Transpose
CommandControlEnable 6002, blnEnabled, True ' &Paste

Couldn't have figured this out if it wasn't for WorkHorse's initial code! THNX!!!

[RobDog888]

Seems it would have been easier to Protect the sheet and add passwording. If they disable macros then all that commandbar work is pointless.

[Siddharth Rout]

I found this code somewhere on the web...

This will also take care of the toolbar button...

Hope this is what you want....

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub
 
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub
 
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub
 
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

[byyu]

Seems it would have been easier to Protect the sheet and add passwording. If they disable macros then all that commandbar work is pointless.

Nope, this code runs even when your workbook, vba project, and sheets are protected. My sheet is protected beyond the extent it needs to be, but thats just me. My sheet has only like 15 lines of real useful code, the rest of the 300+ lines are for protection and formating and all the other stuff.

*edit* haha, I miss read what you said, yes if they disable the macros, it would be kinda useless... crap! now what am i suppose to do Well... if they do eliminate the macros, my sheet would be uterly useless to them then.

Also, protecting the sheet does not always work (but locking every cell would work). But if you lock all the cells, then ppl can't input values or data or whatever in the excel sheet, and that is what some people want to do. Basically let the people use the tool, but not mess with it or screw any of it up.

I needed the "prevent paste feature" because some cells (input cells to the formulas etc) are hidden, but not locked, so people could have pasted some weird formated cell and it would have eliminated all my conditional formating, regular formating i had (center, font size, BORDERS, etc), also my validation (pick from list).

People always say, "O, just protect the sheet and your fine." NOT THE CASE!!!!!!! For some people anyways...


@ koolsid

I tried using that cutcopymode thing, but it never did anything. I would have that line of code, but I could still copy and paste and all that fun stuff.

Application.CutCopyMode = False

That line of code would be in the worksheet_activate sub, etc. Maybe its just cause I don't fully understand what the "CutCopyMode" means. I thought it was suppose to prevent the user from copying, cuting, AND pasting in excel.

[westconn1]

i think cutcopymode is when you copy a range, the range has active border till you hit esc or return or copy something else

maybe you could lock every cell then have a userform to for user interface