Page 1 of 2 12 LastLast
Results 1 to 10 of 14

clear or empty office clipboard form Excel VBA

This is a discussion on clear or empty office clipboard form Excel VBA within the Excel Questions forums, part of the Question Forums category; I came back to a common problem of clearing the office clipboard from Excel VBA (I have WXP, Office 2003 ...

  1. #1
    New Member
    Join Date
    Oct 2005
    Posts
    37

    Default clear or empty office clipboard form Excel VBA

    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).

    Option Explicit

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

    Type tGUID
    ****lData1 As Long
    ****nData2 As Integer
    ****nData3 As Integer
    ****abytData4(0 To 7) As Byte
    End Type

    Type AccObject
    ****objIA As IAccessible
    ****lngChild As Long
    End Type

    'Global objButton As AccObject
    'Dim accButton As AccObject

    Const WM_GETTEXT = &HD

    Public lngChild As Long, strClass As String, strCaption As String

    Declare Function AccessibleObjectFromWindow Lib "oleacc" _
    ********************************************(ByVal hWnd As Long, ByVal dwId As Long, _
    ******************************************** riid As tGUID, ppvObject As Object) As Long

    Declare Function AccessibleChildren Lib "oleacc" _
    ************************************(ByVal paccContainer As IAccessible, ByVal iChildStart As Long, _
    ************************************ ByVal cChildren As Long, rgvarChildren As Variant, _
    ************************************ pcObtained As Long) As Long

    Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ****************************ByVal lpClassName As String, _
    ****************************ByVal lpWindowName As String) As Long

    Declare Function GetParent Lib "user32" (ByVal hWnd As Long) As Long

    Declare Function EnumChildWindows Lib "user32" (ByVal hwndParent _
    ************************************************As Long, ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

    Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, _
    ******************************************************************ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

    Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, _
    ****************************************************************ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long

    Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, _
    ******************************************************************ByVal hWnd2 As Long, ByVal lpClass As String, ByVal lpCaption As String) As Long





    'Retrieve window class name
    Function GetWndClass(ByVal hWnd As Long) As String
    ****Dim buf As String, retval As Long
    ****buf = Space(256)
    ****retval = GetClassName(hWnd, buf, 255)
    ****GetWndClass = Left(buf, retval)
    End Function

    'Retrieve window title

    Function GetWndText(ByVal hWnd As Long) As String
    ****Dim buf As String, retval As Long
    ****buf = Space(256)
    ****retval = SendMessage(hWnd, WM_GETTEXT, 255, buf)
    ****GetWndText = Left(buf, InStr(1, buf, Chr(0)) - 1)
    End Function

    'The call back function used by EnumChildWindows
    Function EnumChildWndProc(ByVal hChild As Long, ByVal lParam As Long) As Long
    ****Dim found As Boolean
    ****EnumChildWndProc = -1
    ****If strClass > "" And strCaption > "" Then
    ********found = StrComp(GetWndClass(hChild), strClass, vbTextCompare) = 0 And _
    ****************StrComp(GetWndText(hChild), strCaption, vbTextCompare) = 0
    ****ElseIf strClass > "" Then
    ********found = (StrComp(GetWndClass(hChild), strClass, vbTextCompare) = 0)
    ****ElseIf strCaption > "" Then
    ********found = (StrComp(GetWndText(hChild), strCaption, vbTextCompare) = 0)
    ****Else
    ********found = True
    ****End If

    ****If found Then
    ********lngChild = hChild
    ********EnumChildWndProc = 0
    ****Else
    ********EnumChildWndProc = -1
    ****End If
    End Function

    'Find the window handle of a child window based on its class and titie
    Function FindChildWindow(ByVal hParent As Long, Optional cls As String = "", Optional title As String = "") As Long
    ****lngChild = 0
    ****strClass = cls
    ****strCaption = title
    ****EnumChildWindows hParent, AddressOf EnumChildWndProc, 0
    ****FindChildWindow = lngChild
    End Function

    'Retrieve the IAccessible interface from a window handle
    'Reference:Jean Ross,Chapter 17: Accessibility in Visual Basic,Advanced Microsoft Visual Basic 6.0, 2nd Edition
    Function IAccessibleFromHwnd(hWnd As Long) As IAccessible
    ****Dim oIA As IAccessible
    ****Dim tg As tGUID
    ****Dim lReturn As Long

    ****' Define the GUID for the IAccessible object
    ****' {618736E0-3C3D-11CF-810C-00AA00389B71}

    ****With 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
    ****End With
    ****' Retrieve the IAccessible object for the form
    ****lReturn = AccessibleObjectFromWindow(hWnd, 0, tg, oIA)
    ****Set IAccessibleFromHwnd = oIA
    End Function

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

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

    Function FindAccessibleChildInWindow(hwndParent As Long, strName As String, lngRole As Long) As AccObject
    ****Dim oParent As IAccessible
    ****Set oParent = IAccessibleFromHwnd(hwndParent)
    ****If oParent Is Nothing Then
    ********Set FindAccessibleChildInWindow.objIA = Nothing
    ****Else
    ********FindAccessibleChildInWindow = FindAccessibleChild(oParent, strName, lngRole)
    ****End If
    End Function



    'Generic routine to retrieve the window handle of the active window of an Office Application
    Function GetOfficeAppHwnd(app As Object) As Long
    ****GetOfficeAppHwnd = FindWindow(vbNullString, GetOfficeAppWindowTitle(app))
    End Function

    'Retrieve the window handle of the task pane
    'Notice: the task pane window title is localized!
    'You can find out the window class and title using Spy, Inspect32 or other tools
    Function GetOfficeTaskPaneHwnd(app As Object) As Long
    ****GetOfficeTaskPaneHwnd = FindChildWindow(GetOfficeAppHwnd(app), _
    ********************************************"MsoCommandBar", "Task Pane")
    End Function

    'Retrieve the window handle of the clipboard child window inside task pane
    'The window title of the clipboard window seems to be language independent,
    'making it a better start point to searching our UI element than the task pane window
    Function GetOfficeClipboardHwnd(app As Object) As Long
    ****GetOfficeClipboardHwnd = FindChildWindow(GetOfficeAppHwnd(app), , "Collect and Paste 2.0")
    End Function

    'Generic routine to retrieve the window title of the active window of an Office application
    Function GetOfficeAppWindowTitle(app As Object) As String
    ****On Error GoTo ErrorHandler
    ****Select Case app.Name
    ****Case "Microsoft Word"
    ********GetOfficeAppWindowTitle = app.ActiveWindow.Caption & " - " & app.Name
    ****Case Else
    ********GetOfficeAppWindowTitle = app.Name & " - " & app.ActiveWindow.Caption
    ****End Select
    ****Exit Function

    ErrorHandler:
    ****MsgBox "Unsupported Office application!"
    ****GetOfficeAppWindowTitle = ""
    End Function

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

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



    [/b]

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    '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
    JSW: Try and try again: "The way of the Coder!"

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,852

    Default

    This worked for me:

    Code:
    Sub Test()
        With Application.CommandBars("Clipboard")
            .FindControl(ID:=3634).Execute
        End With
    End Sub

  4. #4
    New Member
    Join Date
    Oct 2005
    Posts
    37

    Default

    Quote Originally Posted by Andrew Poulsom
    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.

  5. #5
    New Member
    Join Date
    Oct 2005
    Posts
    37

    Default

    Quote Originally Posted by Joe Was
    '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?

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,852

    Default

    Quote Originally Posted by photonblaster
    Quote Originally Posted by Andrew Poulsom
    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

  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  8. #8
    New Member
    Join Date
    Oct 2005
    Posts
    37

    Default

    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.
    Sub Macro6()
    '
    ' Macro6 Macro
    ' Macro recorded 10/12/2005 by PhotonBlaster
    '

    '
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "1"
    Application.CommandBars("Task Pane").Visible = False
    Sheets("A").Select
    End Sub


    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.

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,852

    Default

    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.

  10. #10
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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..."
    JSW: Try and try again: "The way of the Coder!"

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com