code to add item to menu bar

[Ricardo2]

I'm trying to create an add-in and add it as an item on the menu bar. I copied the code from an Excel book. I was doing an F8 to check each line of code and when I get to the line:
Application.CommandBars(1).Controls("&Accounting Dept").Controls.Add
I get a message "Object doesn't support this property or method". I copied it exactly except for changing the names. I'm trying to understand the logic but still new at this.

Const MenuItemName = "Income Accruals"
Const MenuItemMacro = "GetForm"

Private Sub Workbook_Open()
' Create a new menu item
Set NewMenu = Application.CommandBars(1).Controls.Add
' Add a caption
NewMenu.Caption = "&Accounting Dept"
' Create a new menu item
Set NewItem = Application.CommandBars(1).Controls("&Accounting Dept").Controls.Add
' Specify the Caption and OnAction properties
NewItem.Caption = MenuItemName
NewItem.OnAction = MenuItemMacro
' Add separator bar before the menu item
NewItem.BeginGroup = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete existing item before close
Application.CommandBars(1).Controls("&Accounting Dept").Controls(MenuItemName).Delete
End Sub

I appreciate any help.

Thanks.

Ricardo

 

[Mudface]

Have a look at the link below, the 'Menumakr' download should help you out: -

http://j-walk.com/ss/excel/tips/tip53.htm

 

[Ricardo2]

Mudface,

I'm starting out very simply with a new item on the menu bar with one submenu (in Menumakr, Level 1 & 2, no 3). Can I just modify the first two rows on the MenuSheet and delete the rest? I want to make sure if its that simple.

Thanks.

Ricardo
This message was edited by Ricardo2 on 2002-04-15 11:53

 

[Mudface]

The following should do what you want: -

Private Sub Workbook_Open()

Call CreateMenu

End Sub

' Put the following in a standard module

Option Explicit
Const MenuItemName = "Income Accruals"
Const MenuItemMacro = "GetForm"

Public Sub CreateMenu()

Dim MenuObject As CommandBarPopup
Dim MenuItem As CommandBarButton

On Error Resume Next
Application.CommandBars(1).Controls("&Accounting Dept").Delete
On Error GoTo 0

Set MenuObject = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, _
Temporary:=True)
MenuObject.Caption = "&Accounting Dept"

Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
With MenuItem
.Caption = MenuItemName
.OnAction = MenuItemMacro
.BeginGroup = True
End With

End Sub


HTH .

 

[Ricardo2]

Mudface,

I modified the MenuSheet using only the two rows I needed. It works!

Menumakr rocks!

Thanks for the help!

 

[poleary2000]

The MenuMaker worked great. I have a follow up question however. Is there any way I can deactivate one of the menu choices based on what sheet the user is on? If I could do this, that would be awesome. Any suggestions?