Deleting a Menu

Built-in and custom controls can be deleted using the control's Delete method. The following macro deletes the Custom menu:

Public Sub RemoveCustomMenu() Dim cbr As CommandBar

On Error Resume Next

Set cbr = CommandBars("Worksheet Menu Bar") cbr.Controls("Custom").Delete

End Sub

On Error is used in case the menu has already been deleted.

You can use a built-in command bar's Reset method to make the entire command bar revert to its default layout and commands. This is not a good idea if users have other workbooks or add-ins that alter the setup, because all their work will be lost.

The following event procedures should be added to the ThisWorkbook module to add the Custom menu when the workbook is opened, and delete it when the workbook is closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call RemoveCustomMenu

End Sub

Private Sub Workbook_Open()

Call AddCustomMenu End Sub

It is important to recognize that command bar changes are permanent. If you do not remove the Custom menu in this example, it will stay in the Excel Worksheet menu bar during the current session and future sessions. Trying to use this menu with another workbook active could cause unexpected results.

0 0

Post a comment