I use the Activate and Inactivate events for something like this. Also,
before creating the buttons his deletes them if they are there. You're
deleting and recreating the buttons each time one of the workbooks is
activated and just deleting them each time if they are only inactivated.
Since opening included activation and closing includes inactivation, I think
you'll have your bases covered. Also, they are defined as temporary, so
they'll be gone in any event when you close Excel.

Private Sub Workbook_Activate()
Call delete_buttons 'so you don't end up with duplicates
Call create_buttons
End Sub

Private Sub Workbook_Deactivate()
Call delete_buttons
End Sub

Sub create_buttons()
Dim cbar As CommandBar
Dim cbarbutton As CommandBarButton

Set cbar = Application.CommandBars("Worksheet Menu Bar")
With cbar
Set cbarbutton = .Controls.Add(temporary:=True)
With cbarbutton
.Caption = "Box Units"
.Style = msoButtonCaption
.OnAction = "NameBoxes"
End With
Set cbarbutton = .Controls.Add(temporary:=True)
With cbarbutton
.Caption = "Clear Sheets"
.Style = msoButtonCaption
.OnAction = "ClearAll"
End With
End With
End Sub

Sub delete_buttons()
Dim cbar As CommandBar

Set cbar = Application.Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next 'in case the controls don't exist yet
With cbar
.Controls("Box Units").Delete
.Controls("Clear Sheets").Delete
End With
On Error GoTo 0
End Sub

hth,

Doug Glancy

"R. Choate" wrote in message
...
I have code in my "This Workbook" module to add 2 buttons to the menu bar.
Here is the current code for that:

With CommandBars("Worksheet Menu Bar").Controls.Add
.Caption = "Box Units"
.Style = msoButtonCaption
.OnAction = "NameBoxes"
End With

With CommandBars("Worksheet Menu Bar").Controls.Add
.Caption = "Clear Sheets"
.Style = msoButtonCaption
.OnAction = "ClearAll"
End With

I need to replace this with better code AND add code to my BeforeClose
event to delete the buttons. The kicker is that there might
be several copies of each button if the user has opened more than one copy
of the file or has opened it multiple times. I need to
have the code delete all possible instances (copies) of each of these 2
buttons. Any good ideas?

--
RMC,CPA