Adding & removing menu buttons

[R. Choate]

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?

 

[Tom Ogilvy]

Sub Tester1()
On Error Resume Next
Set cBar = CommandBars("Worksheet Menu Bar")
Do
cBar.Controls("Box Units").Delete
cBar.Controls("Clear Sheets").Delete
Set ctl = Nothing: Set ctl1 = Nothing
Set ctl = cBar.Controls("Box Units")
Set ctl1 = cBar.Controls("Clear Sheets")
Loop Until ctl Is Nothing And ctl1 Is Nothing
On Error GoTo 0

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

End Sub

BeforeClose will be much more complicated. You will need to check if the
controls exist and which workbook their onAction property points to. Then
if it is to this workbook, check if there are any other copies of this
workbook open and if so reassign the existing controls to point to code in
one of those workbooks - or if not, then delete the controls.

The modification assumes the code executed by each button (onaction macro)
is general in operation and doesn't work just on the workbook that created
the controls. If this is not true, you would have to make the onaction code
more general since only one set of buttons will exist at any one time.

--
Regards,
Tom Ogilvy

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

 

[Chip Pearson]

Put a unique value in the Tag property of the control when you
create it, and then use FindControls with the Tag value to find
and delete your controls. E.g.,

To create,

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

And, to delete,

Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars. _
FindControls(Tag:="your tag value")
Ctrl.Delete
Next Ctrl



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Doug Glancy]

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

 

[Tom Ogilvy]

Here is a delete version that will work in Excel 97 which does not have
FindControls (may or may not be an issue)

Sub Tester2A()

Dim Ctrl As Office.CommandBarControl
Set Ctrl = Application.CommandBars. _
FindControl(Tag:="your tag value")
Do While Not Ctrl Is Nothing
Ctrl.Delete
Set Ctrl = Application.CommandBars. _
FindControl(Tag:="your tag value")
Loop


End Sub

Should work in later versions as well.