asked on

Delete commandbar in Excel 2003 with VBA

Hello everyone

I've created a custom commandbar. I wrote following code to delete it once the workbook has been closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("My Macros").Delete 'delete the menu item
End Sub

Open in new window

When I close the workbook (within Excel) and then open a new file, the toolbar is still visible. Strangly, when I open the same file again, I end up having two commandbars.

Any idea what I am doing wrong?

Thanks

Massimo

SiddharthRout

Show me the Workbook Open event?

Are you recreating the control there?

Sid

Chris Bottomley

You presumably added the commandbar with a appliaction.commandbars.add command and if so then try:

Application.CommandBars("My Macros").Delete

Chris

Massimo Scola

ASKER

Sidd: Yes, it is in Workbook_Open. Here a part of the code:

    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar

Open in new window

Shouldn't it be in Workbook_Open() ?

Massimo

SiddharthRout

There is one line missing after line 5?

cmbControl.Caption = "My Macros"

Now try it.

Sid

SiddharthRout

Sorry missed your question.

>> Shouldn't it be in Workbook_Open() ?

It should. :)

I believe since you didn't set the caption so on close it was not deleting the commandbar.

Sid

Massimo Scola

ASKER

Sid, I tried it but the menus/commandbar do not go away when I close the file.

Here is the entire code

Private Sub Workbook_Open()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
    cmbControl.Caption = "My Macros"
    With cmbControl
        .Caption = "&IG Arbeit Logistik" 'names the menu item
       
 With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "Aufträge Shopping Taxi" 'adds a description to the menu item
            .OnAction = "AufträgeShoppingTaxi" 'runs the specified macro
            .FaceId = 1098 'assigns an icon to the dropdown
        End With
        
         
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Abonnement Typ"
            .OnAction = "Abos"
            .FaceId = 7098
        End With
       
        
    End With
    Sheets("Start").Activate
End Sub
 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("My Macros").Delete 'delete the menu item
End Sub

Open in new window

strange, isn't it?

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Bottomley

Basically the commandbar was never called "my Macros" and that is why it's deletion was not working.

Delelting your original with that in my previous post will hopefully work.

Chris

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'in case the menu item has already been deleted
    Application.CommandBars("Worksheet Menu Bar").Controls("&IG Arbeit Logistik").Delete 'delete the menu item
End Sub

Open in new window

SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

ah... right!
Thanks a lot!

Massimo

Massimo Scola

ASKER

thanks