ExcelBanter - View Single Post - How do you .DELETE a (sub-menu) Control that was clicked?

Jim Cone

Why not create the menu in the workbook_open event and delete the
menu in the workbook_close event? Problem would be solved.
-or-
You could add a button from the Forms toolbar to the sheet at the same time that
you create the menu. The button could delete itself after removing the sub-menus.
I use that technique in my "List Files" add-in.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Air_Cooled_Nut"
wrote in message
In a nutshell, when I click on the menu sub-item "Exit Admin Mode" all the
sub-items are deleted EXCEPT the "Exit Admin Mode" sub-item. If I step
through the BuildMoreMenus() routine it WILL delete all sub-items if they are
present, including the "Exit Admin Mode" sub-item. Very odd. How can I have
that sub-item deleted when I click it?

Here's the code that creates and deletes the Help menu sub-items:

Code:

Public Const DATA_MENU_ID As Long = 30010  'Help main menu item

Code:

Private Sub BuildMoreMenus()
Dim NewItem As CommandBarButton, cbItem As CommandBarPopup

Call DeleteMoreMenus
Set cbItem = Application.CommandBars(1).FindControl(ID:=DATA_MENU_ID)
If cbItem Is Nothing Then
    MsgBox "Cannot add menu item."
    Exit Sub
Else
    Set NewItem = cbItem.Controls.Add(Type:=msoControlButton)
    With NewItem
        .FaceId = 1826
        .Caption = "Show All Sheets"
        .OnAction = "ShowAll"
        .BeginGroup = True
    End With
    Set NewItem = cbItem.Controls.Add(Type:=msoControlButton)
    With NewItem
        .FaceId = 1835
        .Caption = "Hide All Sheets"
        .OnAction = "HideAll"
        .BeginGroup = False
    End With
    Set NewItem = cbItem.Controls.Add(Type:=msoControlButton)
    With NewItem
        .FaceId = 1640
        .Caption = "Exit Admin Mode"
        .OnAction = "ExitAdmin"
        .BeginGroup = False
    End With
End If
Application.CommandBars(1).FindControl(ID:=HELP_MENU_ID).Tag = "ON"
End Sub

Public Function ExitAdmin()
'Return workbook to user mode.

Call DeleteMoreMenus
End Function

Public Function DeleteMoreMenus()
'Remove the items from main menu.
    
On Error Resume Next
With Application.CommandBars(1).FindControl(ID:=HELP_MENU_ID)
    .Controls("Show All Sheets").Delete
    .Controls("Hide All Sheets").Delete
    .Controls("Exit Admin Mode").Delete  'Doesn't work here.  Because it's 
selected???
    .Tag = "OFF"
End With
End Function