About Us

Air_Cooled_Nut[_11_]

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

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

Air_Cooled_Nut[_2_]

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*?

"Jim Cone" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Disable the Delete Control in the Edit Menu	Virtual Hillbilly	Excel Programming	1	July 23rd 06 10:50 AM
Problem: Control Toolbox Control resizes when clicked	Ed	Excel Programming	1	July 27th 05 07:55 AM
Determine last clicked object / control	Rex	Excel Programming	3	July 15th 05 07:02 PM
How to determine which dynamically generated control was clicked	llowwelll[_7_]	Excel Programming	4	May 23rd 04 09:26 PM
Return Index of Control Object Clicked!	gr8guy	Excel Programming	3	May 11th 04 05:24 AM