[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

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
0
Massimo Scola
Asked:
Massimo Scola
  • 4
  • 4
  • 3
2 Solutions
 
SiddharthRoutCommented:
Show me the Workbook Open event?

Are you recreating the control there?

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

Application.CommandBars("My Macros").Delete

Chris
0
 
Massimo ScolaAuthor Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
There is one line missing after line 5?

cmbControl.Caption = "My Macros"

Now try it.

Sid
0
 
SiddharthRoutCommented:
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
0
 
Massimo ScolaAuthor Commented:
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?
0
 
Chris BottomleyCommented:
That'll be:

Application.CommandBars("Worksheet Menu Bar").Controls("&IG Arbeit Logistik").Delete 'delete the menu item

and delte that new line.

Chris
0
 
Chris BottomleyCommented:
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

0
 
SiddharthRoutCommented:
Chris is right absolutely.

I went as per you earlier code. Your commandbar was never called "my Macros" :)

Sid
0
 
Massimo ScolaAuthor Commented:
ah... right!
Thanks a lot!

Massimo
0
 
Massimo ScolaAuthor Commented:
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now