Reset Clear ClipBoard

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Reset Clear ClipBoard

Post by YasserKhalil »

Hello everyone
I have searched a lot and tried a lot of codes for clearing the clipboard and this is one of them

Code: Select all

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

Public Function ClearClipboard()
    OpenClipboard (0&)
    EmptyClipboard
    CloseClipboard
End Function

Sub Test()
    Call ClearClipboard
End Sub
But nothing works for me till now
Any idea?

User avatar
Jay Freedman
Microsoft MVP
Posts: 1312
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: Reset Clear CliBoard

Post by Jay Freedman »

That code works for me, under ordinary circumstances. However, the documentation for the OpenClipboard function says that "If the function fails, the return value is zero" and that "OpenClipboard fails if another window has the clipboard open". To see if this is what's happening in your test, modify the code to this:

Code: Select all

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

Public Function ClearClipboard()
    If OpenClipboard(0&) Then
        EmptyClipboard
        CloseClipboard
    Else
        MsgBox "OpenClipboard failed"
    End If
End Function

Sub Test()
    Call ClearClipboard
End Sub

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear CliBoard

Post by YasserKhalil »

Thanks a lot.
I have tested the code when the clipboard window is open and it didn't work even if I closed the window
I have closed the workbook and restart the test and I didn't open the clipboard at all and copied some contents and run the code then opened the clipboard and it is clear

So it seems I have not to open this window
Is there a way to close this window and clear the clipboard??

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Reset Clear CliBoard

Post by Doc.AElstein »

I always thought that
Application.CutCopyMode = False
was the way to do it. But if you search the internet, you find that it is sometimes said that this does not clear all the clipboard??
Possibly there is more to it then I thought?
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

Thanks a lot Mr. Alan
I already tested this line but it doesn't clear the clipboard for me

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reset Clear ClipBoard

Post by HansV »

How about this?

In the Visual Basic Editor, insert a userform, then delete it. This seems strange, but it adds a reference that we need.

Code: Select all

Sub ClearClipboard()
    Dim oData As New DataObject
    oData.SetText Text:=Empty
    oData.PutInClipboard
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

Thanks a lot Mr. Hans
I don't know if these codes are working for you or not ... But in fact they didn't work for me
The only that works under specific circumstances is the Jay's code .. But I have not to open the clipboard window at all so as to get the required task

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reset Clear ClipBoard

Post by HansV »

I don't know why you have so many problems with the code.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

My PC is clear from any virus and I have office 2016 professional edition and it works with no problems at all. I am using Windows 7 32 Bit (No problem with the OS too)
Me too don't know why these codes didn't work for me ..
May be the regional settings of Arabic language causes some problems !! :(

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reset Clear ClipBoard

Post by HansV »

Do you have a specific reason for clearing the clipboard?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

Not specific reason in fact. But this was a question of one of my friends that has the same problem with those codes

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Reset Clear ClipBoard

Post by Doc.AElstein »

I copied this directly from Rory Archibald’s Forum Tools, and just added the test routine
Sub TestClearClipboard()

I am afraid I have no idea how it works or what it is doing. I just thought it might be worth a try.
At first glance it may be almost the same as you have already tried.

Code: Select all

' http://excelmatters.com/excel-forums/   Rory and Others
Option Explicit
#If Mac Then
    ' ignore
#Else
    #If VBA7 Then
        Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
        Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As Long
        Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
                                                             ByVal dwBytes As LongPtr) As LongPtr

        Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
        Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As Long
        Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long

        Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
                                                         ByVal lpString2 As Any) As LongPtr

        Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat _
                                                                As Long, ByVal hMem As LongPtr) As LongPtr
    #Else
        Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
        Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
        Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
                                                     ByVal dwBytes As Long) As Long

        Declare Function CloseClipboard Lib "User32" () As Long
        Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
        Declare Function EmptyClipboard Lib "User32" () As Long

        Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
                                                 ByVal lpString2 As Any) As Long

        Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
                                                        As Long, ByVal hMem As Long) As Long
    #End If
#End If
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
'
'

Sub TestClearClipboard()
Dim StrNull As String:
 Let StrNull = vbNullString
 Call ClipBoard_SetData(MyString:=StrNull)
End Sub

'
'
Function ClipBoard_SetData(MyString As String)
    #If Mac Then
        With New MSForms.DataObject
            .SetText MyString
            .PutInClipboard
        End With
    #Else
        #If VBA7 Then
            Dim hGlobalMemory As LongPtr
            Dim hClipMemory   As LongPtr
        #Else
            Dim hGlobalMemory As Long
            Dim hClipMemory   As Long
        #End If

        Dim lpGlobalMemory    As Long
        Dim x                 As Long

        ' Allocate moveable global memory.
        '-------------------------------------------
        hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

        ' Lock the block to get a far pointer
        ' to this memory.
        lpGlobalMemory = GlobalLock(hGlobalMemory)

        ' Copy the string to this global memory.
        lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

        ' Unlock the memory.
        If GlobalUnlock(hGlobalMemory) <> 0 Then
            MsgBox "Could not unlock memory location. Copy aborted."
            GoTo OutOfHere2
        End If

        ' Open the Clipboard to copy data to.
        If OpenClipboard(0&) = 0 Then
            MsgBox "Could not open the Clipboard. Copy aborted."
            Exit Function
        End If

        ' Clear the Clipboard.
        x = EmptyClipboard()

        ' Copy the data to the Clipboard.
        hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:

        If CloseClipboard() = 0 Then
            MsgBox "Could not close Clipboard."
        End If
    #End If

End Function
Last edited by Doc.AElstein on 18 Feb 2019, 11:51, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

Thanks a lot for your interest Mr. Alan
But the same problem .. It seems that the problem is related somewhat to the regional settings as I noticed those codes are working fine for all of you

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Reset Clear ClipBoard

Post by YasserKhalil »

I have found this code

And when testing I got the message "Unable to locate the ""Clear All"" button!" ... Although the button is there !!!!!
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reset Clear ClipBoard

Post by HansV »

It looks like that code was for Excel 2003 or earlier.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Reset Clear ClipBoard

Post by Doc.AElstein »

I copied all the coding to a fresh normal code module
I ran the routine Sub ClearOfficeClipboard()

I did this in
Excel 2007
and
Excel 2003
I get the same error Unable to locate the Clear All button.JPG : https://imgur.com/0tLjHZa" onclick="window.open(this.href);return false;

I have no idea what or where that buttonn should be??
Where should the button be?
What should it look like?
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reset Clear ClipBoard

Post by HansV »

The 'Clear All' button is located on the Clipboard task pane. In Excel 2007 and later it can be displayed by clicking the arrow in the lower right corner of the Clipboard group of the Home tab of the ribbon.
S2535.png
One can make this task pane visible using VBA using

Application.DisplayClipboardWindow = True

I tried inserting that into the code, but it still failed with the same error.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Reset Clear ClipBoard

Post by Doc.AElstein »

Thanks Hans,
that looks like a handy thing to be able to take a peak in …_
ClearAll Clipboard.JPG : https://imgur.com/Jgi4UFR" onclick="window.open(this.href);return false;
_.. and when you click on things they appear in the spreadsheet and you can copy from them … how very useful….
Manual Copy From the Clipboard.JPG : https://imgur.com/bcbTTJ8" onclick="window.open(this.href);return false;

_._____________________
I tried adding the extra code line in both Excel 2007 and 2003, and got the same error again
Unable to locate the Clear All button again.JPG : https://imgur.com/mEda8LM" onclick="window.open(this.href);return false;
XL 2003 Unable again.jpg : https://imgur.com/cX3aKGy" onclick="window.open(this.href);return false;
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Reset Clear ClipBoard

Post by rory »

IIRC for Office 2013 or later, you need to use CommandBars("Office Clipboard") rather than CommandBars("Task Pane")

You can also reduce the code (courtesy of Jaafar Tribak) to:

Code: Select all

Option Explicit

Private Type POINTAPI
  x As Long
  Y As Long
End Type

Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
End Type
#If VBA7 Then
    Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
    Declare PtrSafe Function BringWindowToTop Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    #If Win64 Then
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal arg1 As LongPtr, ppacc As Any, pvarChild As Variant) As Long
    #Else
        Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    #End If
    Dim hwndClip As LongPtr
    Dim hwndScrollBar As LongPtr
    Dim lngPtr As LongPtr
#Else
    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long
    Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long
    Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
    Dim hwndClip As Long
    Dim hwndScrollBar As Long
#End If

Const GW_CHILD = 5
Const S_OK = 0

Sub ClearOfficeClipBoard()

    Dim tRect1 As RECT, tRect2 As RECT
    Dim tPt As POINTAPI
    Dim oIA As IAccessible
    Dim vKid  As Variant
    Dim lResult As Long
    Dim i As Long
    Static bHidden As Boolean
      
    If CommandBars("Office Clipboard").Visible = False Then
        bHidden = True
        CommandBars("Office Clipboard").Visible = True
        Application.OnTime Now, "ClearOfficeClipBoard": Exit Sub
    End If

    hwndClip = FindWindowEx(Application.hwnd, 0, "EXCEL2", vbNullString)
    hwndClip = FindWindowEx(hwndClip, 0, "MsoCommandBar", CommandBars("Office Clipboard").NameLocal)
    hwndClip = GetNextWindow(hwndClip, GW_CHILD)
    hwndScrollBar = GetNextWindow(GetNextWindow(hwndClip, GW_CHILD), GW_CHILD)
    
    If hwndClip And hwndScrollBar Then
        GetWindowRect hwndClip, tRect1
        GetWindowRect hwndScrollBar, tRect2
        BringWindowToTop Application.hwnd
        For i = 0 To tRect1.Right - tRect1.Left Step 50
            tPt.x = tRect1.Left + i: tPt.Y = tRect1.Top - 10 + (tRect2.Top - tRect1.Top) / 2
            #If VBA7 And Win64 Then
                CopyMemory lngPtr, tPt, LenB(tPt)
                lResult = AccessibleObjectFromPoint(lngPtr, oIA, vKid)
            #Else
                lResult = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, vKid)
            #End If
            If InStr("Clear All - Borrar todo - Effacer tout", oIA.accName(vKid)) Then
                Call oIA.accDoDefaultAction(vKid): CommandBars("Office Clipboard").Visible = Not bHidden: bHidden = False: Exit Sub
            End If
            DoEvents
        Next i
    End If
    CommandBars("Office Clipboard").Visible = Not bHidden
    MsgBox "Unable to clear the Office Clipboard"

End Sub
Regards,
Rory

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Reset Clear ClipBoard

Post by Doc.AElstein »

The last coding from Rory gives me the same error, but I only tried in Excel 2003 and Excel 2007
By the way, none of the coding in this Thread, or a few variations that I have tried, seems to clear the clipboard in Excel 2003 or Excel 2007, assuming this might be what we are talking about clearing?? Er Clipboard May Bee.JPG : https://imgur.com/zFc9nz2" onclick="window.open(this.href);return false;
I suppose it might not bee … The clipboard I have heard is a bit difficult to predict and there is a few of them with a few different versions of stuff in them**…


The codings may be clearing something… somewhere ?? Probably anyone's geuss where and what..


Ref
http://www.eileenslounge.com/viewtopic. ... 49#p246687" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 20#p246887" onclick="window.open(this.href);return false;
https://docs.microsoft.com/en-us/office ... -clipboard" onclick="window.open(this.href);return false;
http://excelmatters.com/2013/10/04/late ... ataobject/" onclick="window.open(this.href);return false;
https://stackoverflow.com/questions/250 ... y-on-the-c" onclick="window.open(this.href);return false;
https://stackoverflow.com/questions/250 ... 7#54960767" onclick="window.open(this.href);return false;
https://docs.microsoft.com/de-de/office ... -clipboard" onclick="window.open(this.href);return false;
https://docs.microsoft.com/de-de/office ... -clipboard" onclick="window.open(this.href);return false;
https://social.msdn.microsoft.com/Forum ... rum=isvvba" onclick="window.open(this.href);return false;
https://wellsr.com/vba/2015/tutorials/v ... ste-clear/" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 38#p246698" onclick="window.open(this.href);return false;
http://www.cpearson.com/excel/clipboard.aspx" onclick="window.open(this.href);return false;
http://excelmatters.com/2013/10/04/late ... ataobject/" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic.php?f=30&t=31849" onclick="window.open(this.href);return false;
** https://www.mrexcel.com/forum/excel-que ... ost4043472" onclick="window.open(this.href);return false;
Last edited by Doc.AElstein on 03 Mar 2019, 12:45, edited 4 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also