Disable paste from Office Clipboard

[Jon von der Heyden]

Hi All

I thought I would share a small solution to a problem that has niggled me for a while. I hope it helps someone and perhaps indeed there is a better approach? At the very least I know I can come back here in the future should I need it again.

I want to stop users from using paste that will overwrite my formats and validation rules. However I do not want to prevent paste, not from Excel contents nor from other applications contents. The following code will:

1. Only effect the change where global constant g_blnDESIGN_MODE = False
2. Place the contents of the Office clipboard into the Windows clipboard
3. Note the contents of the Windows clipboard, as Text
4. Clear the clipboard (remove formats)
5. Put the string back into the clipboard
6. Clear the Office clipboard

Public Sub NoPasteAll()
    Dim dobClipboard        As MSForms.DataObject
    Dim strData             As String
    Dim blnEvents           As Boolean
    
    If g_blnDESIGN_MODE Then Exit Sub
    
    With Application
        blnEvents = .EnableEvents
        .EnableEvents = False
    End With
    
    Set dobClipboard = New MSForms.DataObject
    
    On Error Resume Next
        With dobClipboard
            Call .GetFromClipboard
            strData = .GetText
            Call .Clear
            Call .SetText(strData)
            Application.CutCopyMode = False
            Call .PutInClipboard
        End With
    On Error GoTo 0
    
    Application.EnableEvents = blnEvents
    
    Set dobClipboard = Nothing
End Sub

I call the routine each time the Workbook is activated, a sheet is activated or a selection change occurs:

Private Sub Workbook_Activate()
    Call NoPasteAll
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Call NoPasteAll
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call NoPasteAll
End Sub

Note - it requires a reference to MSForms library.

 

[RoryA]

6. Clear the Office clipboard

Where does it do this?

Note - it requires a reference to MSForms library.

Why not late bind it?

Also, I assume you are aware of the bug with the DataObject and Windows 8 or later?

 

[Jon von der Heyden]

Where does it do this?

I clear the dob, not the Clipboard. Good point!

Why not late bind it?

Good idea!

Also, I assume you are aware of the bug with the DataObject and Windows 8 or later?

Nope! What's that all about?

 

[RoryA]

If you have File Explorer open, you end up with two square characters on the clipboard instead of the actual text you intended.

 

[Jon von der Heyden]

Wowza that's cr4p!

 

[RoryA]

Yup. API calls are safer.

 

[Jon von der Heyden]

Ugh so I suppose I ought to reacquaint myself with the API. Ok will do. Meanwhile I took your advise and went with late binding (pinched from your www here).

Also I was getting 'Out of Memory' error which I am not entirely sure is resultant of this (too infrequent to categorically identify). I wonder if it was because of the 'On Errror Resume Next' which I now swapped for an exit label.

Public Sub NoPasteAll()
    Dim objClipboard        As Object
    Dim strData             As String
    Dim blnEvents           As Boolean
    
    If g_blnDESIGN_MODE Then Exit Sub
    
    With Application
        blnEvents = .EnableEvents
        .EnableEvents = False
    End With
    
    Set objClipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    
    On Error GoTo end_Proc
        With objClipboard
            Call .GetFromClipboard
            strData = .GetText
            Call .Clear
            Call .SetText(strData)
            Application.CutCopyMode = False
            Call .PutInClipboard
        End With
    On Error GoTo 0
    
end_Proc:
    Application.EnableEvents = blnEvents
    
    Set objClipboard = Nothing
End Sub