Tom’s Tutorials For Excel: *Really* Clear the Clipboard

Tom’s Tutorials For Excel: *Really* Clear the Clipboard

The Windows Clipboard is a temporary storage area for information that you have copied or moved from one place and plan to use somewhere else. You cannot see or touch the Clipboard but you can work with it to copy, cut, paste, and clear data.

There are some 30 types of data which can be copied onto your Clipboard beyond just text and formulas, such as graphics, charts, and hyperlinks. To truly empty the Clipboard requires more than just pressing the Esc key or executing the VBA codeline Application.CutCopyMode = False.

With Windows API, this VBA macro named ClearClipboard clears all data types on your Clipboard:

Public Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long 
Public Declare Function CloseClipboard Lib "user32" () As Long 
Public Declare Function EmptyClipboard Lib "user32" () As Long 

Sub ClearClipboard() 
OpenClipboard (0&) 
EmptyClipboard 
CloseClipboard 
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: *Really* Clear the Clipboard
  1. Matthew Skomal says:

    Tom, when I paste this into a module or workbook the top 3 Public Declarations are in RED, and won’t compile. Any idea why? I’ve never used a Public Declaration before, so obviously I don’t understand what I’m doing.

    • Tom Urtis says:

      The first likely suspect I would rule out is the double quote characters. On some browsers, they copy into the modules as a different double quote character than the one on our keyboard. So do this, take a close look at the code and methodically one by one, carefully delete each double quote character and in its place type in the double quote character from your keyboard. When you are sure that you have nailed every double quote character, try the code again. I know it works, so post back with how this fix attempt turns out for you.

      • Harvey Acosta says:

        I get a compile error : code must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.

        • Tom Urtis says:

          You probably need this in your module (to replace the code from my web page) to comply with your 64 bit system:

          #If VBA7 Then
          Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As LongPtr
          Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
          Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
          #Else
          Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
          Public Declare Function CloseClipboard Lib "user32" () As Long
          Public Declare Function EmptyClipboard Lib "user32" () As Long
          #End If

          Sub ClearClipboard()
          OpenClipboard (0&)
          EmptyClipboard
          CloseClipboard
          End Sub

  2. David Bangs says:

    I’m trying this in Windows 10 from Excel VBA.
    The code compiles and does throw any errors, but afterward, if I do Win+V, all the content is still on the clipboard.

    DoEvents

    Call WriteLog(bdm, “Clearing Excel Clipboard”)
    Application.CutCopyMode = False

    Call WriteLog(bdm, “Clearing Windows Clipboard”)
    OpenClipboard (0&)
    EmptyClipboard ‘ REVIEW – This does not seem to clear the windows clipboard.
    CloseClipboard

    DoEvents

    Call WriteLog(bdm, “ClearClipboard Finished”, True)

  3. David Bangs says:

    Oops. – The code I pasted in got munged and is unreadable. I three API calls are there and I can step through with VBA debugger and note that after EmptyClipboard, Win+V still has the list of items.

    • Tom Urtis says:

      There is quite a difference between clearing the clipboard to truly make it empty, and just executing the codeline
      Application.CutCopyMode = False
      to assume (as some people incorrectly do) that said codeline empties the clipboard, when really it does not.

      That codeline simply does what it says, which is, if you are in CutCopyMode, such as by selecting a cell and hitting Edit > Copy or Edit > Cut, then executing that codeline will exit that mode.

      There might be one or more of over 30 data formats supported by Windows and Excel which may be on the clipboard having nothing to do with CutCopyMode.

      The below macro evaluates what format(s) if any are on the clipboard.

      The code looks lengthy but that is to look for each data type. I might have left out 2 or 3 so if someone sees one I missed please post it, but this is the general idea:

      Sub TestClipboard()
      Dim vaFormats As Variant, dFormat As Variant
      vaFormats = Application.ClipboardFormats
      For Each dFormat In vaFormats
      Select Case dFormat
      Case xlClipboardFormatBIFF
      MsgBox "xlClipboardFormatBIFF (" & dFormat & ")"
      Case xlClipboardFormatBIFF2
      MsgBox "xlClipboardFormatBIFF2 (" & dFormat & ")"
      Case xlClipboardFormatBIFF3
      MsgBox "xlClipboardFormatBIFF3 (" & dFormat & ")"
      Case xlClipboardFormatBIFF4
      MsgBox "xlClipboardFormatBIFF4 (" & dFormat & ")"
      Case xlClipboardFormatBinary
      MsgBox "xlClipboardFormatBinary (" & dFormat & ")"
      Case xlClipboardFormatBitmap
      MsgBox "xlClipboardFormatBitmap (" & dFormat & ")"
      Case xlClipboardFormatCGM
      MsgBox "xlClipboardFormatCGM (" & dFormat & ")"
      Case xlClipboardFormatCSV
      MsgBox "xlClipboardFormatCSV (" & dFormat & ")"
      Case xlClipboardFormatDIF
      MsgBox "xlClipboardFormatDIF (" & dFormat & ")"
      Case xlClipboardFormatDspText
      MsgBox "xlClipboardFormatDspText (" & dFormat & ")"
      Case xlClipboardFormatEmbeddedObject
      MsgBox "xlClipboardFormatEmbeddedObject (" & dFormat & ")"
      Case xlClipboardFormatEmbedSource
      MsgBox "xlClipboardFormatEmbedSource (" & dFormat & ")"
      Case xlClipboardFormatLink
      MsgBox "xlClipboardFormatLink (" & dFormat & ")"
      Case xlClipboardFormatLinkSource
      MsgBox "xlClipboardFormatLinkSource (" & dFormat & ")"
      Case xlClipboardFormatLinkSourceDesc
      MsgBox "xlClipboardFormatLinkSourceDesc (" & dFormat & ")"
      Case xlClipboardFormatMovie
      MsgBox "xlClipboardFormatMovie (" & dFormat & ")"
      Case xlClipboardFormatNative
      MsgBox "xlClipboardFormatNative (" & dFormat & ")"
      Case xlClipboardFormatObjectDesc
      MsgBox "xlClipboardFormatObjectDesc (" & dFormat & ")"
      Case xlClipboardFormatObjectLink
      MsgBox "xlClipboardFormatObjectLink (" & dFormat & ")"
      Case xlClipboardFormatOwnerLink
      MsgBox "xlClipboardFormatOwnerLink (" & dFormat & ")"
      Case xlClipboardFormatPICT
      MsgBox "xlClipboardFormatPICT (" & dFormat & ")"
      Case xlClipboardFormatPrintPICT
      MsgBox "xlClipboardFormatPrintPICT (" & dFormat & ")"
      Case xlClipboardFormatRTF
      MsgBox "xlClipboardFormatRTF (" & dFormat & ")"
      Case xlClipboardFormatScreenPICT
      MsgBox "xlClipboardFormatScreenPICT (" & dFormat & ")"
      Case xlClipboardFormatStandardFont
      MsgBox "xlClipboardFormatStandardFont (" & dFormat & ")"
      Case xlClipboardFormatStandardScale
      MsgBox "xlClipboardFormatStandardScale (" & dFormat & ")"
      Case xlClipboardFormatSYLK
      MsgBox "xlClipboardFormatSYLK (" & dFormat & ")"
      Case xlClipboardFormatTable
      MsgBox "xlClipboardFormatTable (" & dFormat & ")"
      Case xlClipboardFormatText
      MsgBox "xlClipboardFormatText (" & dFormat & ")"
      Case xlClipboardFormatToolFace
      MsgBox "xlClipboardFormatToolFace (" & dFormat & ")"
      Case xlClipboardFormatToolFacePICT
      MsgBox "xlClipboardFormatToolFacePICT (" & dFormat & ")"
      Case xlClipboardFormatVALU
      MsgBox "xlClipboardFormatVALU (" & dFormat & ")"
      Case xlClipboardFormatWK1
      MsgBox "xlClipboardFormatWK1 (" & dFormat & ")"
      Case dFormat = -1
      MsgBox "Nothing is on the clipboard"
      Case Else
      MsgBox "Clipboard has data on it of the format number (" & dFormat & ")"
      End Select
      Next
      End Sub

      You can easily test the fact that
      Application.CutCopyMode = False
      does not empty the clipboard, by copying a drawing object, or a line of text from the formula bar, or an embedded combobox…whatever the case may be, and execute the codeline
      Application.CutCopyMode = False
      but you will still be able to paste that object or text elsewhere in Excel, as many times as your system memory allows, unless you reboot the computer.

      Notice also, the dynamic of “Paste” being enabled in the Edit or right-click menu is not a reliable way to assume that the clipboard is cleared. It only means that data which can be pasted onto an Excel worksheet might or might not be on the clipboard.

      An example is to start with an empty clipboard and copy a file name from Windows Explorer. Now that copied file will be paste-able into another folder despite the Paste option not being enabled if you attempted to paste onto an Excel worksheet, meaning, the clipboard contains that file data even if you execute the codeline
      Application.CutCopyMode = False
      despite an Excel worksheet not being a compatible destination interface.

Leave a Reply to David Bangs Cancel reply

Your email address will not be published.

*