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, _
Befo=HelpIndex, Temporary:=True)
' Add a caption
NewMenu.Caption = "&Macros"
End Sub

Any help would be greatly appreciated.
Thank you
Renato

You can rename the AddNewMenu to Auto_open
or
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
workbook.
http://j-walk.com/ss/excel/tips/tip53.htm

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

It really makes updates pretty simple.


Renato wrote:

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, _
Befo=HelpIndex, Temporary:=True)
' Add a caption
NewMenu.Caption = "&Macros"
End Sub

Any help would be greatly appreciated.
Thank you
Renato

--

Dave Peterson

Thanks a lot, Dave.
I greatly appreciate your help.

"Dave Peterson" wrote:

You can rename the AddNewMenu to Auto_open
or
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
workbook.
http://j-walk.com/ss/excel/tips/tip53.htm

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

It really makes updates pretty simple.


Renato wrote:

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, _
Befo=HelpIndex, Temporary:=True)
' Add a caption
NewMenu.Caption = "&Macros"
End Sub

Any help would be greatly appreciated.
Thank you
Renato

--

Dave Peterson