You can create your own custom toolbar (not worksheet menu bar) and attach
it to any workbook. Problem is the toolbar (just like the menu bar) does not
disappear when the workbook you attached it to is closed. A copy of the
toolbar remains and appears along with the other built-in toolbars. However,
you can use a macro to remove the toolbar whenever the workbook closes.

The simplest approach:

1. Open the workbook you want the toolbar to reside in (and make sure it's
the active one)

2. Use the Tools--Customize command to create a new custom toolbar (New
button on the Toolbars tab)

3. Add the desired button(s)/commands to the new custom toolbar

4. If you need to attach any of them to macros, right-click the custom
button and use the "Assign Macro" command on the shortcut menu

5. When finished creating the toolbar, click the Toolbars tab

6. Click the "Attach" button and use the Copy button in the Attach dialog to
copy the new toolbar (on the left side of the dialog) into the active
workbook (right side of dialog)

7. Click OK, then close the Customize dialog

8. Acess the VBE (ALT+F11) and double-click the "ThisWorkbook" for the
workbook containing the custom toolbar.

9. Copy the routine below into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Custom 1").Delete
End Sub

Where "Custom 1" is the name of your custom toolbar

10. Save the workbook

When the workbook opens, so does the toolbar. It disappears (gets deleted)
whent he workbook closes.

Note: If you need to make changes to the toolbar, you'll need to re-copy the
updated toolbar into the workbook using steps
--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"Richard m" wrote in message
...

I am trying to create a custom Main Menu for a spreadsheet.
I can install macro in the thisworksheet, workbook to edit the
Worksheet menu bar and when the spreadsheet opens, it will edit the
main menu. But...




- I have not been successfull in reseting the worksheet menu bar when
the spreadsheet closes. How do you reset the Worksheet menu bar when
you click the close box?
- Can a custom worksheet menu bar be created and attached to work
with only one spreadsheet? And how?



Here is the code I am playing with to develop a custom menu. The second
sub is not working.


Code:
--------------------

Private Sub Workbook_Open()
Application.CommandBars("Tools").Controls(2).Delet e

End Sub

Private Sub Application_WorkbookBeforeClose(ByVal playmenu As Workbook,
_
Cancel As Boolean)
Application.CommandBars("Tools").Controls.Add Type:=msoControlButton,
ID:= _
793, Befo=2



End Sub

--------------------



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/