Adding a new menu to a menu bar

In this section, I describe how to use VBA to add a new menu to the Worksheet Menu Bar. The Worksheet Menu Bar is the first item in the CommandBars collection, so you can reference it one of two ways:

♦ By its name: CommandBars("Worksheet Menu Bar")

In VBA terms, you use the Add method to append a new control to the Controls collection. The new control is a pop-up control of type msoControlPopup. You can specify the new control's position; if you don't, the new menu is added to the end of the menu bar.

Adding a new menu is a two-step process:

1. Use the Add method to create an object variable that refers to the new control. Arguments for the Add method enable you to specify the control's type, its ID (useful only if you're adding a built-in menu), its position, and whether it's a temporary control that will be deleted when Excel closes.

2. Adjust the properties of the new control. For example, you'll probably want to specify a Caption property and an OnAction property.

0 0

Post a comment