add a menu bar when document opens

[Todd]

Hoe do I get a macro to auto open and auto remove? I have
the macros below that I want to add a menu item when I
open the workbook its in and remove it when I close the
workbook. I have been playing with it and can't get it to
work.

Can anyone help?



Todd







Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(,
30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , ,
True)
.Caption = "&OpenSupportingDocs"
.OnAction = ThisWorkbook.Name
& "OpenSupportingDocs"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl
(Tag:="MenuItemTag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

 

[Todd]

Thank you!

-----Original Message-----
Hi,



Put those codes into the module ThisWorkbook for the event Activate to
create the bar and inDeactivate to delete it.


--
JP
(e-mail address removed)
http://www.solutionsvba.com





.

 

[Todd]

I am not doing this right yet! I trust the codes because
I have them curtesy of this news group. It always seems I
miss something very basic!


1. Another menu bar is added each time I open the
workbook. How do I stop that?

2. The button added doesn't run my macro. It says it
can't find it? The name seems correct and I have tried
pasting it into every module plus ThisWorkbook.

3. The auto close isn't working yet. I tried
the suggested code and Private Sub App_WorkbookDeactivate
(ByVal Wb As Workbook)as well. But it must be something
else.



Thanks,


Todd

 

[J.E. McGimpsey]

The first problem is a direct result of the third - make sure you
use the correct syntax in the BeforeClose event (I didn't):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MenuBar_Item_Item_Delete
End Sub

The second is because your line

.OnAction = ThisWorkbook.Name & "OpenSupportingDocs"

is missing an exclamation point:

.OnAction = ThisWorkbook.Name & "!OpenSupportingDocs"

I've posted a workbook that adds and deletes the menubar here:

ftp://ftp.mcgimpsey.com/excel/Todd_demo.xls

 

[steve]

Todd,

The code to create should be in the open or activate macro, and the code to
delete should be in the close or deactivate macro. All of these should be
in
the ThisWorkBook module.
Pick form these...

Private Sub Workbook_Open()
* create code*
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
*delete code*
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
*create code*
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
*delete code*
End Sub

Be sure that your create code starts out by deleting the bar and than build
it.

Your on-action code should be in a standard module, NOT in a sheet module.
Or in the ThisWorkBook module.

Also make sure that you do not have duplicate macros of any kind with the
same name...

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/MenuActiveWbDemo.zip

It's in the "Menu Routines" section on page:
http://www.bygsoftware.com/examples/examples.htm

It contains VBA code that will activate a menu only when the workbook it is
in is active.
The code is open and commented.

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"