how to remove command bar button

[Tony]

How I can remove the custom command bar which I want to
add automaticaly when excel file is open.
Adding is done by the following procedure:

Sub Workbook_Open()

Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup

On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
Set cb = c.Controls.Add(msoControlButton, 2)
cb.Style = msoButtonCaption
cb.Caption = "Calculate Totals"
cb.OnAction = "Template 4.xls!
ThisWorkbook.Calculate"
End If

End Sub

Thanks for help.

Regards,

Tony

 

[Rob Bovey]

Hi Tony,

Here's one way to do it:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar") _
.Controls("Calculate Totals").Delete
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Bob Phillips]

Also, when creating it, you can make it temporary

Set cb = c.Controls.Add(msoControlButton, 2,temporary:=true)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)