how to remove command bar button
 

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 procedu

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

how to remove command bar button
 

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 *


"Tony" wrote in message
...
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 procedu

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

how to remove command bar button
 

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)

"Rob Bovey" wrote in message
...
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 *


"Tony" wrote in message
...
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 procedu

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