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:=msoCont rolButton).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?

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



Kent McPherson wrote:

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:=msoCont rolButton).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

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").BeginGr oup = 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?


"Dave Peterson" wrote in message
...
Try
application.commandbars("worksheet.....



Kent McPherson wrote:

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:=msoCont rolButton).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

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


Kent McPherson wrote:

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").BeginGr oup = 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?

"Dave Peterson" wrote in message
...
Try
application.commandbars("worksheet.....



Kent McPherson wrote:

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:=msoCont rolButton).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

--

Dave Peterson

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:=msoCont rolButton).Caption =
"MyMenu"
End Sub

--
HTH...

Jim Thomlinson


"Kent McPherson" wrote:

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:=msoCont rolButton).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?