Results 1 to 14 of 14
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Display Clipboard Window (2002)

    I have a macro that collects several pieces of data from the worksheet into the clipboard. I need to display the clipboard window in the task pane after I do the copying so the user can select the items he wants to paste. I am using the code: Application.DisplayClipboardWindow = True and I get the error that it fails and nothing else. Is this the correct way to display the clipboard or is there another method or object I should be using?
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Display Clipboard Window (2002)

    it should work. Can you post more of the code?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    Hi John,

    Here is the rest of the code. I haven't completed it yet but this is all I have so far. I got stumped on displaying the clipboard window. In the same worksheet, there are five areas separated by an empty row. Each area contains totals that I copy. I'm doing a find on the text "Totals" and then selecting the data and copying it to the clipboard. Then I'm trying to display the clipboard window and for some reason it fails. Thanks for taking a look at it.

    <pre> Dim i As Integer
    Range("A1").Activate
    For i = 1 To 5
    Cells.Find(What:="Totals", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    If i = 3 Or i = 4 or i = 5 Then
    Range(Selection, Selection.End(xlToRight)).Select
    End If
    Selection.Copy
    Next i
    Application.DisplayClipboardWindow = True

    </pre>

    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Display Clipboard Window (2002)

    With this simple test data in a new workbook with no protections set:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><tr><td align=center>1</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    I've attached a stripped down version of the workbook which should help greatly. Please try it and see if you spot something wrong that I can't find. I'm am trying to code it to do the same as what I can do manually. I do a find on "totals", then ctrl+shift+down arrow to select all cells in the column to the bottom of the group. Then ctrl+shift+right arrow to select all cells to the right to the end of the data. Copy the data. Then I do another find for the next group of data.

    The clipboard does not come up. I get a failure message I mentioned earlier. When I display the clipboard manually via Edit | Office Clipboard, I should have 5 copes in my clipboard. I only have 1 copy and that is the data from the last copy that was made (report 5). The four other copies aren't there. If I copy them manually the 5 copies are in the clipboard.

    The attached version has the display clipboard commented out to avoid the error.

    Anyone's help is appreciated.
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Clipboard Window (2002)

    XL2K says that DisplayClipboardWindow is a Macintosh only property. Did that change in 2002?
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Display Clipboard Window (2002)

    Legare, I think it became valid for XP and 2003 when the (IMO very annoying) task pane got introduced.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Display Clipboard Window (2002)

    Hmm. The test I ran successfully in Excel 2003 in <!post=this post,409952>this post<!/post> will not run on XP. It may be that the method doesn't work in XP and the documentation is incorrect.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    Hi Legare,

    In Office 97 it was for Macintosh only. The MSDN documentation in 2002 does not mention Macintosh but because I get the error, I think the documentation is wrong.

    Since I can't bring up the Clipboard window using code directly, is there a way to invoke a menu item (Edit | Office Clipboard), using code? I think there is but I've forgotten how to do it.
    You know it's time to diet when you push away from the table and the table moves.

  10. #10
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    I don't have access to Excel 2003. I am assuming you got a failure message when it tried to execute the DisplayClipboardWindow in XL? Maybe Microsoft fixed the problem in the 2003 version.

    If I use the Macro recorder, and select Edit | Office Clipboard, the macro recorder produces no code!

    As an alternative, is there a way to execute a menu item (Edit | Office Clipboard) from code? I've forgotten how to do it?
    You know it's time to diet when you push away from the table and the table moves.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Clipboard Window (2002)

    You would need to use the SendKeys method to send the keyboard shortcuts.
    Legare Coleman

  12. #12
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    Well I got the clipboard to display using sendkeys easily enough. I'll have to use that code since I can't get DisplayClipboardWindow to work in 2002. Thanks for the help!
    You know it's time to diet when you push away from the table and the table moves.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Display Clipboard Window (2002)

    Hi,
    You can use something like:
    <pre>Sub GetClippy()
    Dim ctlClipOpen As CommandBarButton
    Set ctlClipOpen = CommandBars.FindControl(ID:=809)
    ctlClipOpen.Execute
    End Sub
    </pre>

    Hope that helps.
    Note: you can get at the task pane using Commandbars("Task Pane") but there is no way in Excel of changing to the Clipboard pane, unlike in Word.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. The Following User Says Thank You to rory For This Useful Post:

    AncientBrit (2014-08-20)

  15. #14
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Display Clipboard Window (2002)

    Thanks Rory. This was the VBA code I was thinking off.
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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