My read of your post was that you meant to delete and
restore the 2nd menu item of the Tools menu of the
Worksheet Menu Bar (WMB) as opposed to the second control
on a custom toolbar named "Tools". Your code refers to a
toolbar named "Tools".

I think you're looking for the following. I use the
Deactivate event to reset the WMB because the BeforeClose
event allows the user to cancel after the WMB has been
reset. I make the control invisible instead of deleting
it. Correct for workdwrap of the code.

Private Sub Workbook_Open()
Application.CommandBars(1).Controls("Tools").Contr ols
(2).Visible = False
End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars(1).Reset
End Sub

As for your second question: You can create a custom
toolbar that will only be visible when a certain worksheet
is selected by using the Worksheet_Activate and
Worksheet_Deactivate events as follows. Place the code in
the code module pertaining to the worksheet involved.

Private Sub Worksheet_Activate()
CommandBars("Data").Visible = True
End Sub

Worksheet_Deactivate()
CommandBars("Data").Visible = False
End Sub

Hope it was what you were looking for.

Regards,
Greg

-----Original 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/

.