Excel toolbar/menu problem

[taupirho]

Hi,

I using EXCEL97 and want to add a new menu item at the end of the Worksheet Menu Bar (i.e the one at the top that says File Edit Open etc...) when a specific workbook opens.
When the workbook is closed I want to delete the additional menu item again. I'm using VBA and want to do it programmatically. Any help appreciated.

 

[RobBroekhuis]

Sure. Do you know how to create workbook_open / _beforeclose events? (If not let us know and we'll lead you through the steps).
In the _open event, create the new menu structure:

With Application.CommandBars(1).Controls.Add(msoControlPopup)
.Caption = "MyCustomMenu"
With .Controls.Add
.Caption = "New menu item"
.OnAction = "NewMenuMacro"
End With
with .controls.add
...
end with
End With

and in the _beforeclose event

With Application.CommandBars(1)
.controls(.controls.count).delete
end with

Rob

 

[taupirho]

Spot on Rob , thanks a lot

 

[RobBroekhuis]

I just realized my code (yesterday in a hurry) was a little sloppy. It works if everything goes okay, but usually I would do two things differently:

1. In the beforeclose event, delete the SPECIFIC menu item, rather than the last one:

on error resume next
Application.CommandBars(1).controls("MyCustomMenu&quot.delete

2. In the open event, remove any previously generated menu item of the same name, in case the beforeclose event didn't properly process the last time:

on error resume next
Application.CommandBars(1).controls("MyCustomMenu&quot.delete
on error goto 0

(before the code generating the menu)

Rob

 

[taupirho]

I had already did what your post suggested before I read it but thanks again for taking the trouble.