workbook-specfic Macro Menu in toolbar

D

D Dixon

I have inherited an Excel workbook that once contained a variety o
macros as well as an added menu in the toolbar for running the macros.
The workbook currently does not appear to contain any macros when I vie
the code. However, every attempt I have made to remove the additiona
menu from the toolbar has failed. It always returns upon re-openin
the workbook. If I perform a "Save As" the new file still contains th
additional toolbar menu. If I have multiple copies of the workbook ope
at one time, I get multiple additional menus in the toolbar.

Does anyone know how such a menu can be attached to a spreadsheet s
that it gets added to the toolbar everytime the workbook, or subsequen
copy is opened (and is removed upon closing the workbook). Ultimately
want to have the ability to remove or alter the additional menu.

Thanks
 
M

Myrna Larson

Can you identify any code that creates it? It would be in a general module in
the workbook. Otherwise, this could be a menu change made in Excel 5 with its
menu editor. That editor doesn't exist any more. I believe Jim Rech knows how
to get rid of these menus.
 
R

Ron de Bruin

Hi D Dixon

With code you can do this

*************************
This in a normal module

Sub MakeToolBar()
Dim bar As CommandBar

On Error Resume Next
Application.CommandBars("MyToolBar").Delete
On Error GoTo 0

With Application.CommandBars.Add(Name:="myToolBar", _
Position:=msoBarTop, MenuBar:=False)
' MenuBar:=True hide the Worksheet menubar

.Protection = msoBarNoCustomize
' Prevent users from accessing the Add or Remove Buttons menu

With .Controls.Add(Type:=msoControlButton)
.Caption = "Click me"
.FaceId = 71
.OnAction = "ToolBarMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete the ToolBar"
.FaceId = 72
.OnAction = "ToolBarMacro"
End With

' this will add a default Excel button
.Controls.Add Type:=msoControlButton, ID:=4
.Visible = True
End With
End Sub

Sub ToolBarMacro()
MsgBox "Hi"
End Sub

Sub DeleteToolBar()
Dim bar As CommandBar
On Error Resume Next
Application.CommandBars("MyToolBar").Delete
On Error GoTo 0
End Sub


*****************************
This in the Thisworkbook module

Private Sub Workbook_Activate()
MakeToolBar
End Sub

Private Sub Workbook_Deactivate()
DeleteToolBar
End Sub
 
D

D Dixon

The workbook does not contain any modules, only three objects (tw
sheets and the workbook) which do not have any code when I attempt t
view it. It very well could have been a menu editor from years gon
by. The oldest version of the workbook that I can find with thi
custom menu attached is from 1995. I will check with some people t
see if they have any editors, menu or other, available to them. I
not, I will give the submitted code a try.

Thanks
 
R

Ron de Bruin

See this Add-in
http://www.bmsltd.ie/MVP/Default.htm
RemoveMenus.zip (12 April 2000, 10k, 7906 downloads)

RemoveMenus is a utility for removing custom menus created by the Excel 5/95 menu editor from workbooks. When a workbook is
converted to Excel 97/2000 format without first removing these menus there is no way to do so in Excel 97/2000. Converting back to
Excel 5/95 format may not be a good option as Excel 97/2000 specific enhancements will be lost. Use File, Open to load the add-in.
"Remove Menu Editor Edits" on the Tools menu will start it. The utility will work on closed workbooks only.
 
D

D Dixon

Thank you very much for the link to the "RemoveMenus.zip" utility.
just used it and it worked perfectly
 
Top