Disable paste from Office Clipboard

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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:
Code:
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.
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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?
 
Upvote 0
If you have File Explorer open, you end up with two square characters on the clipboard instead of the actual text you intended.
 
Upvote 0
Yup. API calls are safer.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top