Activating Custom Menu



I'm recently new to Excel VBA. I've bought several books but I can't seem to
get to the right sections. I've created a custom menu for a specific
template and I just need to activate anytime I open the workbook and close
everytime the workbook closes. Its just a blank menu. Here is the code:

Sub AddNewMenu()
Dim HelpIndex As Integer
Dim NewMenu As CommandBarPopup

' Get Index of Help menu
HelpIndex = CommandBars(1).Controls("Help").Index

' Create the control
Set NewMenu = CommandBars(1) _
.Controls.Add(Type:=msoControlPopup, _
Before:=HelpIndex, Temporary:=True)
' Add a caption
NewMenu.Caption = "&Macros"
End Sub

Any help would be greatly appreciated.
Thank you

Dave Peterson

You can rename the AddNewMenu to Auto_open
you can call AddNewMenu from the Workbook_open event.

Option Explicit
Private Sub Workbook_Open()
Call AddNewMenu
End Sub

The workbook_open event goes in the ThisWorkbook code module.

Auto_open stays in a general module.

You may want to look at the way that John Walkenbach did it in his menumaker.xls

He keeps track of the menu items and associated macros in a worksheet. Then
loops through that worksheet.

It really makes updates pretty simple.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question