clear or empty office clipboard form Excel VBA

photonblaster

New Member
Joined
Oct 4, 2005
Messages
37
I came back to a common problem of clearing the office clipboard from Excel VBA (I have WXP, Office 2003 all updates and service packe) to see if there are any elegant solutions posted on this message board, and found none. In fact, I found that many people suggesting help to clearing the clipboard gave help on clearing the windows clipboard rather than the Office clipboard. I did not even see a solution to this issue (at least something that I rcoginized as a solution, forgive me if I missed one), elegant or otherwise, so I am posting the solution I have so people can use it as they see fit, and to see if anyone has improvement suggestions or other methods of doing it.

I especially would like to know what code I could add to automatically open the clipboard, since the code only seems to work if the clipboard is open.
Of course, if someone has 10 lines of code that will empty the clipboard, I would love to see this rather than using this big module.

I am far from an expert on the solution I have, since, after litterally days of googling I found this solution concept on Guoqiang Wu's Blog. According to him, the "handle" (or whatever the technical term is) to access the office clipboard can change as you open and close Excel (or any office program). So you have to search for the handle first, and then send the appropriate signal to the clipboard. Mr. Wu was kind enough to help me modify his implementation so that the search function results gets cached, and any subsequent calls to the clear clipboard function bypasses the search and just clears the clipboard. I am very much indebted to him since clearing the clipboard was a do or die for my project.

Here is my implementation.
to clear the clipboard from within vba just put the code line
ClearOfficeClipboard.

You should run it at least once during any initialization phase of your code so that the overhead for finding the clipboard "handle" does not slow you up latter when you need fast execution.

Also, this is real pain in the whatever,but for this code to work, the clipboard must be visible!!!! I have not found a work around this. And I can't even find a way to make the clipboard visible from within vba, so I have to do it manually! I just minimize the window size and after months of use I do not even notice it there, but talk about brute force! However, since my project will not execute quickly enough without emptying the clipboard frequently of all the garbage it collects that sucks up memory and really slows things down.


Clear the Clipboard Module: ( I just commented out things when I changed them from Mr. Wu's original version that is more generic since it was supposed to work from any office application).

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Const</SPAN> CHILDID_SELF = 0&
<SPAN style="color:#00007F">Const</SPAN> ROLE_TITLEBAR = &H1&
<SPAN style="color:#00007F">Const</SPAN> ROLE_MENUBAR = &H2&
<SPAN style="color:#00007F">Const</SPAN> ROLE_SCROLLBAR = &H3&
<SPAN style="color:#00007F">Const</SPAN> ROLE_GRIP = &H4&
<SPAN style="color:#00007F">Const</SPAN> ROLE_SOUND = &H5&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CURSOR = &H6&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CARET = &H7&
<SPAN style="color:#00007F">Const</SPAN> ROLE_ALERT = &H8&
<SPAN style="color:#00007F">Const</SPAN> ROLE_WINDOW = &H9&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CLIENT = &HA&
<SPAN style="color:#00007F">Const</SPAN> ROLE_MENUPOPUP = &HB&
<SPAN style="color:#00007F">Const</SPAN> ROLE_MENUITEM = &HC&
<SPAN style="color:#00007F">Const</SPAN> ROLE_TOOLTIP = &HD&
<SPAN style="color:#00007F">Const</SPAN> ROLE_APPLICATION = &HE&
<SPAN style="color:#00007F">Const</SPAN> ROLE_DOCUMENT = &HF&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PANE = &H10&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CHART = &H11&
<SPAN style="color:#00007F">Const</SPAN> ROLE_DIALOG = &H12&
<SPAN style="color:#00007F">Const</SPAN> ROLE_BORDER = &H13&
<SPAN style="color:#00007F">Const</SPAN> ROLE_GROUPING = &H14&
<SPAN style="color:#00007F">Const</SPAN> ROLE_SEPARATOR = &H15&
<SPAN style="color:#00007F">Const</SPAN> ROLE_TOOLBAR = &H16&
<SPAN style="color:#00007F">Const</SPAN> ROLE_STATUSBAR = &H17&
<SPAN style="color:#00007F">Const</SPAN> ROLE_TABLE = &H18&
<SPAN style="color:#00007F">Const</SPAN> ROLE_COLUMNHEADER = &H19&
<SPAN style="color:#00007F">Const</SPAN> ROLE_ROWHEADER = &H1A&
<SPAN style="color:#00007F">Const</SPAN> ROLE_COLUMN = &H1B&
<SPAN style="color:#00007F">Const</SPAN> ROLE_ROW = &H1C&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CELL = &H1D&
<SPAN style="color:#00007F">Const</SPAN> ROLE_LINK = &H1E&
<SPAN style="color:#00007F">Const</SPAN> ROLE_HELPBALLOON = &H1F&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CHARACTER = &H20&
<SPAN style="color:#00007F">Const</SPAN> ROLE_LIST = &H21&
<SPAN style="color:#00007F">Const</SPAN> ROLE_LISTITEM = &H22&
<SPAN style="color:#00007F">Const</SPAN> ROLE_OUTLINE = &H23&
<SPAN style="color:#00007F">Const</SPAN> ROLE_OUTLINEITEM = &H24&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PAGETAB = &H25&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PROPERTYPAGE = &H26&
<SPAN style="color:#00007F">Const</SPAN> ROLE_INDICATOR = &H27&
<SPAN style="color:#00007F">Const</SPAN> ROLE_GRAPHIC = &H28&
<SPAN style="color:#00007F">Const</SPAN> ROLE_STATICTEXT = &H29&
<SPAN style="color:#00007F">Const</SPAN> ROLE_TEXT = &H2A&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PUSHBUTTON = &H2B&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CHECKBUTTON = &H2C&
<SPAN style="color:#00007F">Const</SPAN> ROLE_RADIOBUTTON = &H2D&
<SPAN style="color:#00007F">Const</SPAN> ROLE_COMBOBOX = &H2E&
<SPAN style="color:#00007F">Const</SPAN> ROLE_DROPLIST = &H2F&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PROGRESSBAR = &H30&
<SPAN style="color:#00007F">Const</SPAN> ROLE_DIAL = &H31&
<SPAN style="color:#00007F">Const</SPAN> ROLE_HOTKEYFIELD = &H32&
<SPAN style="color:#00007F">Const</SPAN> ROLE_SLIDER = &H33&
<SPAN style="color:#00007F">Const</SPAN> ROLE_SPINBUTTON = &H34&
<SPAN style="color:#00007F">Const</SPAN> ROLE_DIAGRAM = &H35&
<SPAN style="color:#00007F">Const</SPAN> ROLE_ANIMATION = &H36&
<SPAN style="color:#00007F">Const</SPAN> ROLE_EQUATION = &H37&
<SPAN style="color:#00007F">Const</SPAN> ROLE_BUTTONDROPDOWN = &H38&
<SPAN style="color:#00007F">Const</SPAN> ROLE_BUTTONMENU = &H39&
<SPAN style="color:#00007F">Const</SPAN> ROLE_BUTTONDROPDOWNGRID = &H3A&
<SPAN style="color:#00007F">Const</SPAN> ROLE_WHITESPACE = &H3B&
<SPAN style="color:#00007F">Const</SPAN> ROLE_PAGETABLIST = &H3C&
<SPAN style="color:#00007F">Const</SPAN> ROLE_CLOCK = &H3D&

<SPAN style="color:#00007F">Type</SPAN> tGUID
    lData1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    nData2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    nData3 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    abytData4(0 To 7) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>

<SPAN style="color:#00007F">Type</SPAN> AccObject
    objIA <SPAN style="color:#00007F">As</SPAN> IAccessible
    lngChild <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>

<SPAN style="color:#007F00">'Global objButton As AccObject</SPAN>
<SPAN style="color:#007F00">'Dim accButton As AccObject</SPAN>

<SPAN style="color:#00007F">Const</SPAN> WM_GETTEXT = &HD

<SPAN style="color:#00007F">Public</SPAN> lngChild <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, strClass <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strCaption <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> AccessibleObjectFromWindow <SPAN style="color:#00007F">Lib</SPAN> "oleacc" _
                                            (<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> dwId <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                             riid <SPAN style="color:#00007F">As</SPAN> tGUID, ppvObject <SPAN style="color:#00007F">As</SPAN> Object) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> AccessibleChildren <SPAN style="color:#00007F">Lib</SPAN> "oleacc" _
                                    (<SPAN style="color:#00007F">ByVal</SPAN> paccContainer <SPAN style="color:#00007F">As</SPAN> IAccessible, <SPAN style="color:#00007F">ByVal</SPAN> iChildStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                     <SPAN style="color:#00007F">ByVal</SPAN> cChildren <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rgvarChildren <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, _
                                     pcObtained <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindWindow <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "FindWindowA" ( _
                            <SPAN style="color:#00007F">ByVal</SPAN> lpClassName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _
                            <SPAN style="color:#00007F">ByVal</SPAN> lpWindowName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetParent <SPAN style="color:#00007F">Lib</SPAN> "user32" (<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> EnumChildWindows <SPAN style="color:#00007F">Lib</SPAN> "user32" (<SPAN style="color:#00007F">ByVal</SPAN> hwndParent _
                                                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpEnumFunc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetClassName <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "GetClassNameA" (<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                                                  <SPAN style="color:#00007F">ByVal</SPAN> lpClassName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> nMaxCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> SendMessage <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "SendMessageA" (<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                                                <SPAN style="color:#00007F">ByVal</SPAN> wMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> wParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindWindowEx <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "FindWindowExA" (<SPAN style="color:#00007F">ByVal</SPAN> hWnd1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _
                                                                  <SPAN style="color:#00007F">ByVal</SPAN> hWnd2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpClass <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpCaption <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>





<SPAN style="color:#007F00">'Retrieve window class name</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GetWndClass(<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> buf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, retval <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    buf = Space(256)
    retval = GetClassName(hWnd, buf, 255)
    GetWndClass = Left(buf, retval)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Retrieve window title</SPAN>

<SPAN style="color:#00007F">Function</SPAN> GetWndText(<SPAN style="color:#00007F">ByVal</SPAN> hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> buf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, retval <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    buf = Space(256)
    retval = SendMessage(hWnd, WM_GETTEXT, 255, buf)
    GetWndText = Left(buf, InStr(1, buf, Chr(0)) - 1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'The call back function used by EnumChildWindows</SPAN>
<SPAN style="color:#00007F">Function</SPAN> EnumChildWndProc(<SPAN style="color:#00007F">ByVal</SPAN> hChild <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lParam <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> found <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
    EnumChildWndProc = -1
    <SPAN style="color:#00007F">If</SPAN> strClass > "" And strCaption > "" <SPAN style="color:#00007F">Then</SPAN>
        found = <SPAN style="color:#00007F">StrComp</SPAN>(GetWndClass(hChild), strClass, vbTextCompare) = 0 And _
                <SPAN style="color:#00007F">StrComp</SPAN>(GetWndText(hChild), strCaption, vbTextCompare) = 0
    <SPAN style="color:#00007F">ElseIf</SPAN> strClass > "" <SPAN style="color:#00007F">Then</SPAN>
        found = (StrComp(GetWndClass(hChild), strClass, vbTextCompare) = 0)
    <SPAN style="color:#00007F">ElseIf</SPAN> strCaption > "" <SPAN style="color:#00007F">Then</SPAN>
        found = (StrComp(GetWndText(hChild), strCaption, vbTextCompare) = 0)
    <SPAN style="color:#00007F">Else</SPAN>
        found = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    <SPAN style="color:#00007F">If</SPAN> found <SPAN style="color:#00007F">Then</SPAN>
        lngChild = hChild
        EnumChildWndProc = 0
    <SPAN style="color:#00007F">Else</SPAN>
        EnumChildWndProc = -1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Find the window handle of a child window based on its class and titie</SPAN>
<SPAN style="color:#00007F">Function</SPAN> FindChildWindow(<SPAN style="color:#00007F">ByVal</SPAN> hParent <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> cls <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "", <SPAN style="color:#00007F">Optional</SPAN> title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "") <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    lngChild = 0
    strClass = cls
    strCaption = title
    EnumChildWindows hParent, AddressOf EnumChildWndProc, 0
    FindChildWindow = lngChild
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Retrieve the IAccessible interface from a window handle</SPAN>
<SPAN style="color:#007F00">'Reference:Jean Ross,Chapter 17: Accessibility in Visual Basic,Advanced Microsoft Visual Basic 6.0, 2nd Edition</SPAN>
<SPAN style="color:#00007F">Function</SPAN> IAccessibleFromHwnd(hWnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> IAccessible
    <SPAN style="color:#00007F">Dim</SPAN> oIA <SPAN style="color:#00007F">As</SPAN> IAccessible
    <SPAN style="color:#00007F">Dim</SPAN> tg <SPAN style="color:#00007F">As</SPAN> tGUID
    <SPAN style="color:#00007F">Dim</SPAN> lReturn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

    <SPAN style="color:#007F00">' Define the GUID for the IAccessible object</SPAN>
    <SPAN style="color:#007F00">' {618736E0-3C3D-11CF-810C-00AA00389B71}</SPAN>

    <SPAN style="color:#00007F">With</SPAN> tg
        .lData1 = &H618736E0
        .nData2 = &H3C3D
        .nData3 = &H11CF
        .abytData4(0) = &H81
        .abytData4(1) = &HC
        .abytData4(2) = &H0
        .abytData4(3) = &HAA
        .abytData4(4) = &H0
        .abytData4(5) = &H38
        .abytData4(6) = &H9B
        .abytData4(7) = &H71
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#007F00">' Retrieve the IAccessible object for the form</SPAN>
    lReturn = AccessibleObjectFromWindow(hWnd, 0, tg, oIA)
    <SPAN style="color:#00007F">Set</SPAN> IAccessibleFromHwnd = oIA
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Recursively looking for a child with specified accName and accRole in the accessibility tree</SPAN>
<SPAN style="color:#00007F">Function</SPAN> FindAccessibleChild(oParent <SPAN style="color:#00007F">As</SPAN> IAccessible, strName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lngRole <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> AccObject
    <SPAN style="color:#00007F">Dim</SPAN> lHowMany <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> avKids() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> lGotHowMany <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> oChild <SPAN style="color:#00007F">As</SPAN> IAccessible
    FindAccessibleChild.lngChild = CHILDID_SELF
    <SPAN style="color:#00007F">If</SPAN> oParent.accChildCount = 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> FindAccessibleChild.objIA = <SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    lHowMany = oParent.accChildCount
    <SPAN style="color:#00007F">ReDim</SPAN> avKids(lHowMany - 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    lGotHowMany = 0
    <SPAN style="color:#00007F">If</SPAN> AccessibleChildren(oParent, 0, lHowMany, avKids(0), lGotHowMany) <> 0 <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Error retrieving accessible children!"
        <SPAN style="color:#00007F">Set</SPAN> FindAccessibleChild.objIA = <SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    <SPAN style="color:#007F00">'To do: the approach described in http://msdn.microsoft.com/msdnmag/issues/0400/aaccess/default.aspx</SPAN>
    <SPAN style="color:#007F00">' are probably better and more reliable</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> lGotHowMany - 1
        <SPAN style="color:#00007F">If</SPAN> IsObject(avKids(i)) <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">StrComp</SPAN>(avKids(i).accName, strName) = 0 And avKids(i).accRole = lngRole <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> FindAccessibleChild.objIA = avKids(i)
                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
            <SPAN style="color:#00007F">Else</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> oChild = avKids(i)
                FindAccessibleChild = FindAccessibleChild(oChild, strName, lngRole)
                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> FindAccessibleChild.objIA <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">StrComp</SPAN>(oParent.accName(avKids(i)), strName) = 0 And oParent.accRole(avKids(i)) = lngRole <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> FindAccessibleChild.objIA = oParent
                FindAccessibleChild.lngChild = avKids(i)
                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Function</SPAN> FindAccessibleChildInWindow(hwndParent <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, strName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, lngRole <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> AccObject
    <SPAN style="color:#00007F">Dim</SPAN> oParent <SPAN style="color:#00007F">As</SPAN> IAccessible
    <SPAN style="color:#00007F">Set</SPAN> oParent = IAccessibleFromHwnd(hwndParent)
    <SPAN style="color:#00007F">If</SPAN> oParent <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> FindAccessibleChildInWindow.objIA = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        FindAccessibleChildInWindow = FindAccessibleChild(oParent, strName, lngRole)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>



<SPAN style="color:#007F00">'Generic routine to retrieve the window handle of the active window of an Office Application</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GetOfficeAppHwnd(app <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    GetOfficeAppHwnd = FindWindow(vbNullString, GetOfficeAppWindowTitle(app))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Retrieve the window handle of the task pane</SPAN>
<SPAN style="color:#007F00">'Notice: the task pane window title is localized!</SPAN>
<SPAN style="color:#007F00">'You can find out the window class and title using Spy, Inspect32 or other tools</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GetOfficeTaskPaneHwnd(app <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    GetOfficeTaskPaneHwnd = FindChildWindow(GetOfficeAppHwnd(app), _
                                            "MsoCommandBar", "Task Pane")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Retrieve the window handle of the clipboard child window inside task pane</SPAN>
<SPAN style="color:#007F00">'The window title of the clipboard window seems to be language independent,</SPAN>
<SPAN style="color:#007F00">'making it a better start point to searching our UI element than the task pane window</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GetOfficeClipboardHwnd(app <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    GetOfficeClipboardHwnd = FindChildWindow(GetOfficeAppHwnd(app), , "Collect and Paste 2.0")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Generic routine to retrieve the window title of the active window of an Office application</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GetOfficeAppWindowTitle(app <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrorHandler
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> app.Name
    <SPAN style="color:#00007F">Case</SPAN> "Microsoft Word"
        GetOfficeAppWindowTitle = app.ActiveWindow.Caption & " - " & app.Name
    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
        GetOfficeAppWindowTitle = app.Name & " - " & app.ActiveWindow.Caption
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>

ErrorHandler:
    MsgBox "Unsupported Office application!"
    GetOfficeAppWindowTitle = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#007F00">'Using Active Accessibility to clear Office clipboard</SPAN>
<SPAN style="color:#007F00">'Assumption:</SPAN>
<SPAN style="color:#007F00">'    this is running within Word or Excel as a macro, thus the global Application object is available</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ClearOfficeClipboard()
    <SPAN style="color:#00007F">Static</SPAN> accButton <SPAN style="color:#00007F">As</SPAN> AccObject
    <SPAN style="color:#00007F">If</SPAN> accButton.objIA <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> fShown <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
        fShown = CommandBars("Task Pane").Visible
        wsRAW_DATA.Select
        Range("A1").Copy
        bSuccess = oAIX.Sleep(2000)
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (fShown) <SPAN style="color:#00007F">Then</SPAN>
            CommandBars("Task Pane").Enabled = <SPAN style="color:#00007F">True</SPAN>
            CommandBars("Task Pane").Visible = <SPAN style="color:#00007F">True</SPAN>
            <SPAN style="color:#007F00">'                        MsgBox ("Press OK and Open Clipboard , then F5 to continue or you will get an error message")</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#007F00">'             CommandBars("Task Pane").Visible = True</SPAN>
        <SPAN style="color:#007F00">'           WordBasic.EditOfficeClipboard  '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$</SPAN>
        accButton = FindAccessibleChildInWindow(GetOfficeClipboardHwnd(Application), "Clear All", ROLE_PUSHBUTTON)
        <SPAN style="color:#007F00">'                CommandBars("Task Pane").Visible = fShown</SPAN>
        <SPAN style="color:#007F00">'                  CommandBars("Task Pane").Visible = False</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> accButton.objIA <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Unable to locate the ""Clear All"" button!"
    <SPAN style="color:#00007F">Else</SPAN>
        accButton.objIA.accDoDefaultAction accButton.lngChild
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">''Using Active Accessibility to clear Office clipboard</SPAN>
<SPAN style="color:#007F00">''Input: app - the application object of an Office application</SPAN>
<SPAN style="color:#007F00">''Assumption: Clipboard task pane is shown in the Office application (app object)</SPAN>
<SPAN style="color:#007F00">'Function ClearOfficeClipboard(app As Object) As Boolean</SPAN>
<SPAN style="color:#007F00">'        Dim oButton As AccObject, fShow As Boolean</SPAN>
<SPAN style="color:#007F00">''        Dim fShow As Boolean      '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$</SPAN>
<SPAN style="color:#007F00">'        'Get the IAccessible interface and child id (wrapped in the AccObject type)</SPAN>
<SPAN style="color:#007F00">'        'Notice: the second parameter, accName "Clear All" is localized!</SPAN>
<SPAN style="color:#007F00">'        'You can find out the accName and accRole using Spy, Inspect32 or other tools</SPAN>
<SPAN style="color:#007F00">'        oButton = FindAccessibleChildInWindow(GetOfficeClipboardHwnd(app), "Clear All", ROLE_PUSHBUTTON)</SPAN>
<SPAN style="color:#007F00">'        If oButton.objIA Is Nothing Then</SPAN>
<SPAN style="color:#007F00">'                MsgBox "Unable to locate the ""Clear All"" button!"</SPAN>
<SPAN style="color:#007F00">'                ClearOfficeClipboard = False</SPAN>
<SPAN style="color:#007F00">'        Else</SPAN>
<SPAN style="color:#007F00">'                oButton.objIA.accDoDefaultAction oButton.lngChild</SPAN>
<SPAN style="color:#007F00">'                ClearOfficeClipboard = True</SPAN>
<SPAN style="color:#007F00">'        End If</SPAN>
<SPAN style="color:#007F00">'End Function</SPAN>



</FONT>[/b]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
'Sheet module code
Private Declare Function apiOpenClipboard Lib "user32" _
Alias "OpenClipboard" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiEmptyClipboard Lib "user32" _
Alias "EmptyClipboard" _
() As Long

Private Declare Function apiCloseClipboard Lib "user32" _
Alias "CloseClipboard" _
() As Long


'Standard Module code
Private Declare Sub Sleep Lib "kernel32.dll" ( _
ByVal dwMilliseconds As Long)
Private Declare Function FindWindowEx Lib "user32.dll" _
Alias "FindWindowExA" (ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function PostMessage Lib "user32.dll" Alias _
"PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long

Private Const WM_LBUTTONDOWN As Long = &H201&
Private Const WM_LBUTTONUP As Long = &H202&

'creates a long variable out of two words
Private Function MakeLong(ByVal nLoWord As Integer, ByVal nHiWord As Integer) As Long
MakeLong = nHiWord * 65536 + nLoWord
End Function


Sub ClearOfficeClipboard()
Dim hMain&, hExcel2&, hClip&, hWindow&, hParent&
Dim lParameter&, sTask$

sTask = Application.CommandBars("Task Pane").NameLocal

'Handle for XLMAIN
hMain = Application.hwnd

'Find the OfficeClipboard Window
'2 methods as we're not sure if it's visible
'ONCE it has been made visible the windowclass is created
'and remains loaded for the duration of the instance
Do
hExcel2 = FindWindowEx(hMain, hExcel2, "EXCEL2", vbNullString)

hParent = hExcel2: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoCommandBar", sTask)
If hWindow Then
hParent = hWindow: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
If hClip > 0 Then
Exit Do
End If
End If
End If
Loop While hExcel2 > 0

If hClip = 0 Then
hParent = hMain: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
End If
End If

If hClip = 0 Then
ClipWindowForce
hParent = hMain: hWindow = 0
hWindow = FindWindowEx(hParent, hWindow, "MsoWorkPane", vbNullString)
If hWindow Then
hParent = hWindow: hWindow = 0
hClip = FindWindowEx(hParent, hWindow, "bosa_sdm_XL9", vbNullString)
End If
End If


If hClip = 0 Then
MsgBox "Cant find Clipboard window"
Exit Sub
End If

lParameter = MakeLong(120, 18)
Call PostMessage(hClip, WM_LBUTTONDOWN, 0&, lParameter)
Call PostMessage(hClip, WM_LBUTTONUP, 0&, lParameter)
Sleep 100
DoEvents

End Sub

Sub ClipWindowForce()
Dim octl
With Application.CommandBars("Task Pane")
If Not .Visible Then
Application.ScreenUpdating = False
Set octl = Application.CommandBars(1).FindControl(ID:=809, recursive:=True)
If Not octl Is Nothing Then octl.Execute
.Visible = False
Application.ScreenUpdating = True
End If
End With
End Sub
 
Upvote 0
This worked for me:

Code:
Sub Test()
    With Application.CommandBars("Clipboard")
        .FindControl(ID:=3634).Execute
    End With
End Sub
 
Upvote 0
Andrew Poulsom said:
This worked for me:
Code:
Sub Test()
    With Application.CommandBars("Clipboard")
        .FindControl(ID:=3634).Execute
    End With
End Sub
HI There! Thanks for the tip, I tried it but I get a runtime error 91 on the findcontrol line
"Object Variable or with variable not set"
I have options explicit always set, but I do not see anything I have to declare before executing this sub.
 
Upvote 0
Joe Was said:
'Sheet module code
Private Declare Function apiOpenClipboard Lib "user32" _
Alias "OpenClipboard" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiEmptyClipboard Lib "user32" _
Alias "EmptyClipboard" _
() As Long

Private Declare Function apiCloseClipboard Lib "user32" _
Alias "CloseClipboard" _
() As Long


'Standard Module code
Private Declare Sub Sleep Lib "kernel32.dll" ( _
ByVal dwMilliseconds As Long)
Private Declare Function FindWindowEx Lib "user32.dll" _

etc
End Sub

Thanks for the code, Joe.
I put the sheet module section at the top of ThisWorkbook module in the MS Excel Ojbects sheets list, and the rest into its own module (created Module20 and pasted it in there). Hope this is the right way to paste it into my code. Compiled OK.
I then had the office clipboard visible, with some junk in it, set the mouse inside the sub ClearOfficeClipboard() and pressed F5. Sub executed, but clipboard was not cleared.

hClip was set to 4130502, if this helps.

Did I implement your code right?
 
Upvote 0
photonblaster said:
Andrew Poulsom said:
This worked for me:
Code:
Sub Test()
    With Application.CommandBars("Clipboard")
        .FindControl(ID:=3634).Execute
    End With
End Sub
HI There! Thanks for the tip, I tried it but I get a runtime error 91 on the findcontrol line
"Object Variable or with variable not set"
I have options explicit always set, but I do not see anything I have to declare before executing this sub.

If you turn on the macro recorder, right click any Toolbar, select Clipboard and turn off the macro recorder, what code do you get? I got this:

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/10/2005 by Poulsom
'

'
    Application.CommandBars("Clipboard").Visible = True
End Sub
 
Upvote 0
First you can find the Index for your Toolbars with this code:

Sub myBars()
'Sheet Module Code, like: Sheet1.
Dim r&, cb As Object

r = 3
For Each cb In CommandBars
r = r + 1
ActiveSheet.Cells(r, 1).Value = cb.Name
ActiveSheet.Cells(r, 2).Value = cb.Index
ActiveSheet.Cells(r, 3).Value = cb.Type
Next cb

Columns("A:C").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Sub myXBar()
'Sheet Module Code, like: Sheet1.

'In mycase the ClipBoard was Index: 126 So,
MsgBox CommandBars(126).Name
End Sub

Second:
This is how to use the code I first posted!

Sub myClr()
'Sheet Module Code, like: Sheet1.

Call ClearOfficeClipboard
apiOpenClipboard (0)
apiEmptyClipboard
apiCloseClipboard
Application.CutCopyMode = False
End Sub
 
Upvote 0
If you turn on the macro recorder, right click any Toolbar, select Clipboard and turn off the macro recorder, what code do you get? I got this:

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/10/2005 by Poulsom
'

'
    Application.CommandBars("Clipboard").Visible = True
End Sub
Thanks again. I remember trying this way back when and it did not work for me. I tried again...the task pane comes into view when the clipboard/taskpane button is pressed, and then I did some garbage keystrokes to make sure things were being recorded, and then closed down the task pane using the X in top rh corner. The task pane opening is not recorded! Closing it is recorded! And on the closing, it is the "Task Pane" that is closed, not the clipboard. As reported earlier post, I have WXP home edition, Office 2003, all up to date on SPs and updates.
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro6()
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">' Macro6 Macro</SPAN>
<SPAN style="color:#007F00">' Macro recorded 10/12/2005 by PhotonBlaster</SPAN>
<SPAN style="color:#007F00">'</SPAN>

<SPAN style="color:#007F00">'</SPAN>
Range("J1").Select
ActiveCell.FormulaR1C1 = "1"
Application.CommandBars("Task Pane").Visible = <SPAN style="color:#00007F">False</SPAN>
Sheets("A").Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

If you say ah ha, just mimic the closing code and put in task pane visible = True instead of false.
Application.CommandBars("Task Pane").Visible = True
This gives a run time error:
-2147467259(80004005)'
method 'visible' of object 'ComandBar' failed.

I ran it several times just to make sure, and found out that if I open and close the task pane manually first, it will now open from the vba code. This defeats the whole purpose of the excecise of course since you have to open it before you can automatically open it, so I ended up just having to remember to open it myself.
 
Upvote 0
I'm still on good old Excel 2000 so I don't have this fancy Task Pane stuff. I found out the ID of the "Clear Clipboard" button like this:

Code:
Sub Test()
    Dim Ctrl As CommandBarControl
    Dim Msg As String
    For Each Ctrl In Application.CommandBars("ClipBoard").Controls
        Msg = Msg & Ctrl.Caption & vbTab & Ctrl.ID & vbCrLf
    Next Ctrl
    MsgBox Msg
End Sub

which for me was 3634. I see it has become "Clear All" in Excel 2003, so maybe the ID has changed. Since you got an error on the FindControl statement in the code I posted, maybe it will work for you if the change the ID.
 
Upvote 0
Andrew,

Currently at work I only have 2003:
In 2003 "ID" is nolonger listed as a valid property of CommandBars("ClipBoard")?

The only way I have been able to get it is with the code I posted above "Sub myBars()" which uses the Index number not ID no matter what string I use for the Index in your Sub?

UpDate:

I found a way to do it whith your code in 2003:

Sub TestCB()
Dim Ctrl As CommandBarControl
Dim Msg As String, r&

'r = 3
For Each Ctrl In Application.CommandBars("EDIT").Controls
'r = r + 1
'ActiveSheet.Cells(r, 5).Value = Ctrl.Caption
'ActiveSheet.Cells(r, 6).Value = Ctrl.ID
Msg = Msg & Ctrl.Caption & vbTab & Ctrl.ID & vbCrLf
Next Ctrl

MsgBox Msg
End Sub


I get 809 for "Office Clip&board..."
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top