Add/Remove Custom Control To Chart Drop-Down Menu

  • I want to add a control under Chart on the menu bar. This line of code errors with "Invalid Procedure Call or Argument"

    Code
    1. With Application.CommandBars("Worksheet menu bar").Controls("Chart")


    Change "Chart" to "File" or "Edit" or "Tools" and it's fine.


    The Chart item only appears when a chart is selected, but it errors even when a chart on the worksheet is selected.


    Thanks in advance for suggestions.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Add Control Under Chart On Worksheet Menu Bar


    Keyword: Chart


    Hey Buddy.


    Try this,



    Simplified to:


    Code
    1. Sub HTH()
    2. With Application.CommandBars("Chart").Controls.Add(Type:=msoControlButton)
    3. .Caption = "My Macro"
    4. .OnAction = "RunMyMacro"
    5. .TooltipText = "Click Me"
    6. .FaceId = 1098
    7. End With
    8. End Sub

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Or did you mean under chart on the insert menu?


    Code
    1. Sub HTH3()
    2. With Application.CommandBars("Worksheet Menu Bar").Controls("Insert")
    3. With .Controls.Add(Type:=msoControlButton, Before:=6)
    4. .Caption = "My Macro"
    5. .OnAction = "RunMyMacro"
    6. .TooltipText = "Click Me"
    7. .FaceId = 1098
    8. End With
    9. End With
    10. End Sub
  • Re: Add Control Under Chart On Worksheet Menu Bar


    Thank you both for replying.


    Reafidy, I meant the Chart item with the drop-down menu that appears on the worksheet menu bar when a chart is selected -- the item that toggles with Data (Data disappears when a chart is selected and Chart appears).


    Reafidy's suggestion this adds the control to the Chart toolbar, rather than the Chart item on the menu bar - so it does what I want, but not where I want it. (sCtl is a string constant)

    Code
    1. With Application.CommandBars("Chart")
    2. .Controls.Add(Type:=msoControlButton).Caption = sCtl
    3. .Controls(sCtl).BeginGroup = True
    4. .Controls(sCtl).OnAction = "'" & ThisWorkbook.Name & "'!ShowForm"
    5. Sheet1.Pictures("Picture 1").Copy
    6. .Controls(sCtl).PasteFace
    7. End With


    A solution around Dave's suggestion works, but it's ugly and has another problem. The code between With/End With is the same as above.


    The problem with this is that I then need to remove the Chart item from the menu bar (to leave the user's environment unchanged) -- unless, oops, he had a chart selected when the add-in was loaded (so it was already showing), and now I delete it and screw up his environment.


    Is there a way to change a drop-down menu (accessed via ID, whatever) even when it's not showing?


    Thanks.


    Edit: Dave, if I do this, what would I do next?

    Code
    1. set cntl = commandbars.findcontrol(type:=msoControlPopup, id:=30022)

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Add Control Under Chart On Worksheet Menu Bar


    Try this in the Private Workbook Module of the add-in;


  • Re: Add/Remove Custom Control To Chart Drop-Down Menu


    Sorry to be slow in responding.


    I've not had a chance to try your method, Dave, and will post back when I do.


    Reafidy, your method worked great on a different menu -- but thinking about it, maybe that's the right place.


    Thanks to you both.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]