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

[Air_Cooled_Nut]

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:

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

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

 

[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:

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

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

 

[Air_Cooled_Nut]

I already do that and that's not what I'm asking about.

When I click on the menu sub-item "Exit Admin Mode" all the sub-items are
deleted EXCEPT the "Exit Admin Mode" sub-item. I still am in the workbook
and still using it, the Admin Mode can be toggled on or off during the use of
the workbook. How can I have that sub-item deleted ("Exit Admin Mode") *when
it is clicked*?