Programming Excel with VBA and .NET

Remove the Menu on Close

Interestingly, temporary menus still persist if the user closes the workbook but not Excel. Therefore, you may want to remove the menu explicitly when the file closes. Why use temporary menus if you are going to delete them anyway? Using a temporary menu ensures that the menu is removed if Excel crashes while the file is open.

The following code removes the top-level menu created by the BuildMenu sample when the workbook closes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Enable error handling in case menu was deleted earlier somehow.
    On Error Resume Next
    ' Make sure temporary menu is deleted.
    RemoveMenu
    On Error GoTo 0
End Sub
 
Sub RemoveMenu( )
    Dim cb As CommandBar, cpop As CommandBarPopup
    ' Get the menu bar (CommandBar).
    Set cb = Application.CommandBars("Worksheet Menu Bar")
    ' Get the top-level menu created by BuildMenu.
    Set cpop = cb.FindControl(msoControlPopup, , "Run2")
    ' Delete it.
    cpop.Delete
End Sub

The FindControl method uses the Tag property of the top-level menu to locate the control so it can be deleted. That is a better technique than locating the menu through its index, which might change if the user or other code adds a new menu.