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
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