Clear clipboard in excel vba

[YasserKhalil]

Hello everyone
I have tried different solutions to clear the clipboard but I need to clear all that exists in clipboard section

oopp.png

I need VBA code to do that task

[HansV]

Do you really need to clear the entire Office Clipboard?

Application.CutCopyMode = False

will clear Excel's clipboard, but not the multi-item Office Clipboard.

[YasserKhalil]

Yes I need to clear the entire Office Clipboard

[HansV]

I tried several methods mentioned in a Google search (using the MSForms DataObject and involving Windows APIs). The only one that worked for me in Microsoft 365 is the following (source: Clearing the clipboard in Office 365):

Code: Select all

Private Declare PtrSafe Function AccessibleChildren Lib "oleacc" (ByVal paccContainer As Office.IAccessible, _
    ByVal iChildStart As Long, ByVal cChildren As Long, _
    ByRef rgvarChildren As Any, ByRef pcObtained As Long) As Long

Sub EvRClearOfficeClipBoard()
    Dim cmnB, IsVis As Boolean, j As Long
    Set cmnB = Application.CommandBars("Office Clipboard")

   With Application
        .DisplayClipboardWindow = True
    End With

    IsVis = cmnB.Visible

    If Not IsVis Then
        cmnB.Visible = True
        DoEvents
    End If

    For j = 1 To 7
        AccessibleChildren cmnB, Choose(j, 0, 3, 0, 3, 0, 3, 1), 1, cmnB, 1
    Next

    cmnB.accDoDefaultAction CLng(0)

    Application.CommandBars("Office Clipboard").Visible = IsVis

    With Application
        .DisplayClipboardWindow = False
    End With
End Sub

Don't ask me to explain the code!

[YasserKhalil]

Amazing. It worked for me too. I already tried several solutions too but nothing worked. This only worked for me
Thank you very much

[PJ_in_FL]

I tried several methods mentioned in a Google search (using the MSForms DataObject and involving Windows APIs). The only one that worked for me in Microsoft 365 is the following (source: Clearing the clipboard in Office 365):

...


Don't ask me to explain the code!

YIKES!

[HansV]

Weird code, isn't it?

[SpeakEasy]

The way it works is by using the fact that a CommandBar implements the Accessibility interface (iAccessible), and we can use that to navigate the GUI hierarchy; a bit like tabbing around a userform

The code navigates it's way down through the hierarchy starting at the root msoCommandBar (and down through to the msoWorkPane, I believe) to the 'Clear All' control And then presses that button (note that the code as written will error if there is nothing in the Office Clipboard)

Given the code uses magic numbers, the question is how did the programmer know what numbers to use? I'd guess either dogged experimentation or, more likely, using a tool like Microsoft's Accessibility Insights for Windows to walk the tree.

[HansV]

Thanks for the insight!