VBA menus - How do I make menu visible when a chart is selected?

[bstuchin]

Hello,
I'm trying to write a simple menu generator script that creates a two-list menu called "Valuation" that has two menu lists "Format Data Table" and "Format Chart." So far, the macro works fine, but the only problem is that the menu disappears when a chart is actually selected. I'd like the menu to stay visible when a chart is selected so that a user can run the macro on the selected chart. Can someone please help me with this? I have a comment in the code for where the "Format Chart" item should be visible.

Thanks in advance!
Blake

Sub Auto_open()
' Sub CreateMenu()
' creates a new menu
' can also be used to create commandbarbuttons
' may be automatically executed from an Auto_Open macro or a Workbook_Open eventmacro
Dim cbMenu As CommandBarControl ', cbSubMenu As CommandBarControl
  RemoveMenu ' delete the menu if it already exists
    ' create a new menu on an existing commandbar (the next 6 lines)
    Set cbMenu = Application.CommandBars(1).Controls.Add(msoControlPopup, , , , True)
    With cbMenu
        .Caption = "Va&luation"
        .Tag = "MyTag"
        .BeginGroup = False
        .Enabled = True
    End With
    If cbMenu Is Nothing Then Exit Sub ' didn't find the menu...

    ' add menuitem to menu
    With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "Format Data Table"
        .OnAction = ThisWorkbook.Name & "!checkForm1"
    End With
    
        With cbMenu.Controls.Add(msoControlButton, 1, , , True)
        .Caption = "Format Chart"
    ' I would like this to be visible if a chart is selected
        .OnAction = ThisWorkbook.Name & "!checkForm2"
    End With
    
End Sub

 

[tusharm]

In 2003 or earlier, put the commands of interest in the appropriate place in the 'Worksheet Menu Bar' commandbar and the 'Chart Menu Bar' commandbar -- rather than identifying a commandbar by number as you did with commandbars(1)

 

[bstuchin]

Hi there,
Thanks so much for your help! Got everything working now. You guys are the best.

Blake

 

[tusharm]

Glad to be of help.