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

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

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


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75228

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

MavrickMark;269788 Wrote:
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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75228

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


--
Gary''s Student - gsnu200839


"MavrickMark" wrote:

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