Workbook_open sub does not work

  • Thread starter Thread starter MavrickMark
  • Start date Start date
M

MavrickMark

I'm attempting to add a toolbar as a menu item when I open a workbook and
delete it when I close. I've used this code in another workbook and it works
fine. I can execute the workbook_open code using the debugger and it works
fine. The WorkBook_beforeClose cannot be executed using the debugger and
does not work on close either. I've posted my code below. Any help is
sincerely 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
End Su
----------------------------------------------------------------------------------------
' 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 Su
------------------------------------------------------------------------------------------------
' 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
 
Sorry, but this newsgroup is intended for questions about macros in Access,
the database product that's part of Office Professional.

Macros in Access are very different that macros in any other Office product
(other Office products use the name macro to describe VBA code)

You'd be best off reposting your question to a newsgroup related to Excel.
 
Back
Top