problem with custom menus

[Tony]

I am trying to add custom menu with few items in Excel
2002. I am getting error message and can not figure out
where is the problem in syntax. Here is my code:

*****************************************

Sub Workbook_Open()
Dim cmbMenu As CommandBarPopup
Dim cmbcMenuItem As CommandBarControl

' Ensure menu doesn't already exist.
RemoveMenus

' Add a new popup menu bar, set it to cmbMenu.
Set cmbMenu = CommandBars("Worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, _
Before:=CommandBars("Worksheet Menu Bar"). _
Controls.Count)

' Set the caption of the new menu.
With cmbMenu
.Caption = "Estimate Macros"
.DescriptionText = "Estimate Macros Menu"
End With

' Add a new menu bar buttons, set it to cmbcMenuItem.
Set cmbcMenuItem = _
cmbMenu.Controls.Add(Type:=msoControlButton)
' Set the properties of the button found in menu
range.
With cmbcMenuItem
.Caption = "Subtotals"
.OnAction = "Sub_total"
End With

' Release variables.

Set cmbcMenuItem = Nothing
Set cmbMenu = Nothing

End Sub

' Remove custom menu defined in CustomMenu range.
Sub RemoveMenus()
On Error Resume Next
' Remove Menu Bar.
CommandBars("Worksheet Menu Bar").Controls("Estimate
Macros").Delete
End Sub
Sub Workbook_BeforeClose(Cancel As Boolean)

RemoveMenus

End Sub

******************************************

Error comes in line

Set cmbMenu = CommandBars("Worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, _
Before:=CommandBars("Worksheet Menu Bar"). _
Controls.Count)

Thanks for advice.

Tony

 

[Tony]

I have it working. The problem was with the placing of the
code. I put them in This Workbook initialy. After moving
the code to the module all is working.

Tony