Adding menu item to Tools

[Kent McPherson]

I'd like to add an item to the Tools menu when I open a spreadsheet and then
delete it before the workbook closes. I've tried adding this code to the
Workbook Open event.

Private Sub Workbook_Open()
CommandBars("Worksheet men
bar").Controls("Tools").Controls.Add(Type:=msoControlButton).Caption="MyMenu"
End Sub

When I open the spreadsheet, I get an error 91: Object Variable or With
block variable not set.

This same codes works if I include it in the code I have for a userform I've
defined to take inputs from the user.

Ideas?

 

[Dave Peterson]

Try
application.commandbars("worksheet.....

I'd like to add an item to the Tools menu when I open a spreadsheet and then
delete it before the workbook closes. I've tried adding this code to the
Workbook Open event.

Private Sub Workbook_Open()
CommandBars("Worksheet men
bar").Controls("Tools").Controls.Add(Type:=msoControlButton).Caption="MyMenu"
End Sub

When I open the spreadsheet, I get an error 91: Object Variable or With
block variable not set.

This same codes works if I include it in the code I have for a userform I've
defined to take inputs from the user.

Ideas?

 

[Kent McPherson]

Thanks. That worked!

I then tried making a group break on the Tools menu with this command added
to my subroutine below after I created the menu item.

CommandBars("Worksheet men
bar").Controls("Tools").Controls("MyMenu").BeginGroup = True

When I execute the delete command, it doesn't remove the item from Tools
menu anymore. It works fine without the group but not with it. Do I have
to remove the group first somehow?

 

[Guest]

Might just be a copy and paste error but you did not specify the menu bar
correctly...

Private Sub Tada()
Application.CommandBars("Worksheet Menu
Bar").Controls("Tools").Controls.Add(Type:=msoControlButton).Caption =
"MyMenu"
End Sub

 

[Dave Peterson]

This worked ok for me:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application.CommandBars("Worksheet menu bar").Controls("Tools")
On Error Resume Next
.Controls("MyMenu").Delete
On Error GoTo 0
End With

End Sub
Private Sub Workbook_Open()

Dim myCtrl As CommandBarControl

With Application.CommandBars("Worksheet menu bar").Controls("Tools")
On Error Resume Next
.Controls("MyMenu").Delete
On Error GoTo 0
Set myCtrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
End With

With myCtrl
.Caption = "MyMenu"
.BeginGroup = True
End With

End Sub