Hi!

I am trying to add a custom item to the menu bar in Excel
when a specific workbook opens, with code as listed
below. The code runs without problems if I execute it
after the workbook has been opened, but I get an error
message if I paste and run the code in the Workbook_Open
procedure. Error message is typical Error 438 or Object
variable not set. I have tried to declare the variable at
a few different places with no luck. Any suggestions?

Johann

Code extract ************

Sub Macro2()

Dim VWSMenu As Object
Dim VWSSub1 As Object
Dim VWSSub2 As Object

Set VWSMenu = CommandBars("Worksheet Menu Bar").Controls.
Add(Type:=msoControlPopup, Befo=11, Temporary:=True)
With VWSMenu
.Caption = "VWS &Menu"
End With

Set VWSSub1 = CommandBars("Worksheet Menu Bar").Controls
("VWS Menu")
With VWSSub1
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = "Leads List"
End With


etc.**************

In the IDE Project Explorer, select the ThisWorkbook
object & open its code page, add this:

Private Sub Workbook_Activate()
Set_Menus
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Kill_Menus
End Sub

Now add a new standard module with the foloowing code:
Option Explicit
Sub Set_Menus()
Dim cmd As CommandBarPopup
Dim ctrl As CommandBarControl
Dim ctrldrop As CommandBarControl
Kill_Menus

With CommandBars("Worksheet Menu Bar")
Set cmd = .Controls.Add(msoControlPopup, _
befo=.Controls.Count, _
temporary:=True)
End With
cmd.Visible = True

With cmd
.Caption = "M&yTools"
With cmd.Controls.Add(msoControlButton)
.Caption = "Ctrl &1"
.Visible = True
.OnAction = "menu1"
End With
With .Controls.Add(msoControlPopup)
.Caption = "Subs 1"
With .Controls.Add(msoControlButton)
.Caption = "Sub1 &1"
.OnAction = "menu2"
End With
With .Controls.Add(msoControlButton)
.Caption = "Sub1 &2"
.OnAction = "menu2"
End With
End With
With .Controls.Add(msoControlPopup)
.Caption = "Subs 2"
With .Controls.Add(msoControlButton)
.Caption = "Sub2 &1"
.OnAction = "menu2"
End With
With .Controls.Add(msoControlButton)
.Caption = "Sub2 &2"
.OnAction = "menu2"
End With
End With
End With

Set cmd = Nothing

End Sub
Sub Kill_Menus()
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls
("MyTools").Delete
On Error GoTo 0
End Sub
Sub menu1()
MsgBox "Menu 1"
End Sub
Sub menu2()
MsgBox "Menu 2"
End Sub



This example places a new menu item before the Help menu,
and demonstrates sun menus etc.

HTH

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi!

I am trying to add a custom item to the menu bar in
Excel
when a specific workbook opens, with code as listed
below. The code runs without problems if I execute it
after the workbook has been opened, but I get an error
message if I paste and run the code in the Workbook_Open
procedure. Error message is typical Error 438 or Object
variable not set. I have tried to declare the variable
at
a few different places with no luck. Any suggestions?

Johann

Code extract ************

Sub Macro2()

Dim VWSMenu As Object
Dim VWSSub1 As Object
Dim VWSSub2 As Object

Set VWSMenu = CommandBars("Worksheet Menu
Bar").Controls.
Add(Type:=msoControlPopup, Befo=11, Temporary:=True)
With VWSMenu
.Caption = "VWS &Menu"
End With

Set VWSSub1 = CommandBars("Worksheet Menu Bar").Controls
("VWS Menu")
With VWSSub1
.Controls.Add(Type:=msoControlPopup,
Befo=1).Caption = "Leads List"
End With


etc.**************
.