Workbook_open not executing

[MavrickMark]

I borrowed and modified some code from another workbook which adds and
deletes custom menus on open, then deletes them when the workbook is closed.
It worked fine in the other workbook but will not execute in the new
workbook. Only mods are the toolbar name. I can execute the workbook_open
sub in debug mode, but the workbook_beforeclose can not be executed...
strange. I've pasted the code below. Any help or suggestions is appreciated.

Mark

Sub Workbook_Open()
Dim standardmenubar As CommandBar
Dim mycommandbar As CommandBar
Dim c As CommandBarControl
Set standardmenubar = Application.CommandBars("worksheet menu bar")
Set mycommandbar = Application.CommandBars("ETO")
mycommandbar.Visible = False

' test if menu already exists
For Each c In standardmenubar.Controls
If c.Caption = mycommandbar.Controls(1).Caption Then
c.Visible = True
If c.Caption = mycommandbar.Controls(2).Caption Then
c.Visible = True
Exit Sub
End If
End If
Next

' menu does not exist: copy
Set c = mycommandbar.Controls(1).Copy(standardmenubar,
standardmenubar.Controls.Count)
' Set c = mycommandbar.Controls(2).Copy(standardmenubar,
standardmenubar.Controls.Count)
c.Visible = True
'UserForm1.Show
End Sub

' delete menu
Sub Workbook_BeforeClose(Cancel As Boolean)
Dim standardmenubar As CommandBar
Dim mycommandbar As CommandBar
Dim c As CommandBarControl
Set standardmenubar = Application.CommandBars("worksheet menu bar")
Set mycommandbar = Application.CommandBars("ETO")

' delete only if menu still exists
For Each c In standardmenubar.Controls
If c.Caption = mycommandbar.Controls(2).Caption Then
c.Delete
End If
Next
For Each c In standardmenubar.Controls
If c.Caption = mycommandbar.Controls(1).Caption Then
c.Delete
End If
Next
mycommandbar.Delete
End Sub

' activate/deactivate menu
Private Sub Workbook_Activate()
With Application.CommandBars("worksheet menu bar")
.Controls("ETO").Visible = True
'.Controls("Organize").Visible = True
End With

End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("worksheet menu bar").Controls("ETO").Visible =
False
'Application.CommandBars("worksheet menu
bar").Controls("Organize").Visible = False
End Sub

 

[royUK]

Don't use error handlers until yo know the code is working, they wil
suppress error messages that would help.

Are you sure that the code is in the correct place, i.e. the workboo
module & not a Standard Module

 

[Simon Lloyd]

Where does the code fall down?, have you made sure you have an activ
menubar visible before deleting any others as you cannot delete the onl
menubar in the worksheet

I borrowed and modified some code from another workbook which adds an
deletes custom menus on open, then deletes them when the workbook i
closed
It worked fine in the other workbook but will not execute in the ne
workbook. Only mods are the toolbar name. I can execute th
workbook_ope
sub in debug mode, but the workbook_beforeclose can not be executed..
strange. I've pasted the code below. Any help or suggestions i
appreciated

Mar

Sub Workbook_Open(
Dim standardmenubar As CommandBa
Dim mycommandbar As CommandBa
Dim c As CommandBarContro
Set standardmenubar = Application.CommandBars("worksheet menu bar"
Set mycommandbar = Application.CommandBars("ETO"
mycommandbar.Visible = Fals

' test if menu already exist
For Each c In standardmenubar.Control
If c.Caption = mycommandbar.Controls(1).Caption The
c.Visible = Tru
If c.Caption = mycommandbar.Controls(2).Caption The
c.Visible = Tru
Exit Su
End I
End I
Nex

' menu does not exist: cop
Set c = mycommandbar.Controls(1).Copy(standardmenubar
standardmenubar.Controls.Count
' Set c = mycommandbar.Controls(2).Copy(standardmenubar
standardmenubar.Controls.Count
c.Visible = Tru
'UserForm1.Sho
End Su

' delete men
Sub Workbook_BeforeClose(Cancel As Boolean
Dim standardmenubar As CommandBa
Dim mycommandbar As CommandBa
Dim c As CommandBarContro
Set standardmenubar = Application.CommandBars("worksheet menu bar"
Set mycommandbar = Application.CommandBars("ETO"

' delete only if menu still exist
For Each c In standardmenubar.Control
If c.Caption = mycommandbar.Controls(2).Caption The
c.Delet
End I
Nex
For Each c In standardmenubar.Control
If c.Caption = mycommandbar.Controls(1).Caption The
c.Delet
End I
Nex
mycommandbar.Delet
End Su

' activate/deactivate men
Private Sub Workbook_Activate(
With Application.CommandBars("worksheet menu bar"
.Controls("ETO").Visible = Tru
'.Controls("Organize").Visible = Tru
End Wit

End Su
Private Sub Workbook_Deactivate(
On Error Resume Nex
Application.CommandBars("worksheet menu bar").Controls("ETO").Visible
Fals
'Application.CommandBars("worksheet men
bar").Controls("Organize").Visible = Fals
End Su

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Gary''s Student]

Private Sub Workbook_Open()

also make sure that you installed it in the proper place:
From the Excel window:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window