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
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.
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.
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.
You probably need this in your module (to replace the code from my web page) to comply with your 64 bit system:
#If VBA7 ThenDeclare 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
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)
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.
There is quite a difference between clearing the clipboard to truly make it empty, and just executing the codeline
Application.CutCopyMode = Falseto 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 = Falsedoes 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 = Falsebut 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 = Falsedespite an Excel worksheet not being a compatible destination interface.