VBA Code to clear the Windows 10 64 bit clipboard

[JeffGrant]

Hi All,

does anybody have vba code to clear the windows 10 64 bit clip board?

I stupidly continue to paste code from the clipboard that isn't correct and it mess up my formulas.

So I figure if I clear the clipboard at the end of the sub, then I cant mess it up

I would like to make my sheets a little bit more fool proof, or in this case Jeff-proof.

There are few references around, but the examples out there are not designed for windows 10 64 bit and so they code fails.

I am not talking about the vba clipboard because that can be cleared with application.cutcopymode = false. I am specifically wanting to clear the windows 10 clipboard.

Thanks in advance

Jeff.

 

[RoryA]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Macro to Clear win 10 64 bit clip board [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

In the code in that linked thread you just need a couple of small changes to the declaration:

#IF VBA7 then
Public Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
Public Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
Public Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
#Else
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long
Public Declare Function CloseClipboard Lib "user32" () As Long
#End If
Public Sub ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Sub

 

[JeffGrant]

Ok Rory, I wasn't aware of that. Also, I set the thread as solved by mistake and thanked him publicly, and I didn't want to insult the person who help in the other forum by saying it worked and then it didn't.

I will be mindful of that in future.

 

[JeffGrant]

Hi Rory, I am using VBA in Excel 365. For whatever reason, I am still getting the Public Declare Statements in red which throws up a compile error.

 

[JeffGrant]

 

[RoryA]

Sorry - brain fart on my part. I've updated the code above. The pit in the #Else part will still turn red but the code will work.

 

[JeffGrant]

No we are talking....bloody rippa...just did a bunch of tests and looks like I can my my code idiot-proof -> jeff-proof.......

thanks you kindly.......

 

[harveya915]

I think this code would be useful for me. Let me explain my situation and you let me know if this applies. In Outlook, I have a TextBox inside a UserForm. I have a CommandButton that when pressed is supposed to copy whatever is in that TextBox onto the clipboard so I can paste it on to Microsoft Teams. I have the following code for that:

Private Sub CommandButton2_Click()
     Dim dataObj As MSForms.DataObject
    Set dataObj = New MSForms.DataObject
   
    With dataObj
        .SetText TextBox4.Text
        .PutInClipboard
    End With
End Sub

Sometimes this works, however, sometimes it doesn't. When I try to paste i get 2 squares instead. So I am assuming it's pasting some sort of special characters from the clipboard?
I have another CommandButton that when clicked clears the contents of the TextBox. Perhaps I can put the code here so that it clears the clipboard entirely so i won't be having the issue with the 2 squares?

 

[JeffGrant]

Hi Harvey.

This is what I use in VBA to clear the clipboard

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

cheers