Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy onto Piv Tab menu (XL2000)

    I would like to add Copy to the menu that appears when you right click on a cell in a pivot table. By using the customise toolbar procedure I can add it OK to the menu that appears when you click the Pivot Table button on the Pivot Table toolbar, but I cannot access the right click menu. Is this possible or will it be necessary to modify the r.c. menu with VBA?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy onto Piv Tab menu (XL2000)

    VBA:

    Option Explicit
    Dim octr As CommandBarControl
    Sub test()
    Set octr = Application.CommandBars("PivotTable Context Menu").Controls.Add(msoControlButton)
    octr.Caption = "Hi there!"
    End Sub

    Sub test1()
    octr.Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy onto Piv Tab menu (XL2000)

    Thanks Jan.

    The code I settled on was:

    Sub test()
    'Set octr = Application.CommandBars("PivotTable Context Menu").Controls.Add(msoControlButton)
    Application.CommandBars("PivotTable Context Menu").Reset
    On Error Resume Next

    With Application.CommandBars("PivotTable Context Menu")
    .Controls("Format Cells...").Delete
    .Controls("Format Report...").Delete
    .Controls("PivotChart").Delete
    .Controls("Hide").Delete
    .Controls("Refresh Data").Delete
    .Controls("Select").Delete
    .Controls("Group and Outline").Delete
    .Controls("Formulas").Delete
    .Controls("Order").Delete
    .Controls("Show Pages").Delete
    .Controls.Add msoControlButton, ID:=19
    .Controls(Application.CommandBars("PivotTable Context Menu").Controls.Count).BeginGroup = True
    End With

    End Sub

    Three points I hope someone can clear up:

    1. Is it possible to clear the whole menu without deleting each control individually? Then add back the wanted items.

    2. The ".Controls("Show Pages").Delete" does not delete the (greyed out) Show Pages item, why not?

    3. If I put the Begin Group line before the Add msoControlButton, ID:=19 line it leaves a blank line at the top of the new group before the Copy item. Why?

    TIA

Posting Permissions

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