How to Add a Section Line to the Menu for VBA With Excel

1. Start Excel 2003.
2. Open the Visual Basic Editor. Select 'Tools,' then 'Macro,' then 'Visual Basic Editor' from the menu.
3. Insert a blank module for your code. Select 'Insert,' then 'Module' from the menu.
4. Start a new subprocedure. Type the following:Sub CustomMenu()Excel will automatically add an 'End Sub' statement.
5. Define variables. Type the following between the 'Sub' and 'End Sub' statements:Dim MenuObject As CommandBarPopupDim MenuItem As Object
6. Delete any existing instances of your custom menu. This prevents duplicates if you run the code more than once. Type the following:On Error Resume NextApplication.CommandBars(1).Controls('My Macro').DeleteOn Error GoTo 0
7. Create a new menu option called 'My Macro.' Use a parameter value of 10 in the 'Set' statement to place it before 'Help,' the 10th item on the Excel menu. Type the following:Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=10, temporary:=True)MenuObject.Caption = 'My Macro'
8. Add two items to the menu, with a section line between them. Create the section line by setting the 'BeginGroup' property in the second item to 'True.'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'MacroName'MenuItem.Caption = 'Run'Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)MenuItem.OnAction = 'About'MenuItem.Caption = 'About Macro'MenuItem.BeginGroup = True
9. Run the code. Select 'Run,' then 'Run Sub/UserForm' from the menu. When you return to the spreadsheet, you will see 'My Macro' on the menu, prior to 'Help.' Click to view the two menu options, separated by a section line.