Solved

Delete commandbar in Excel 2003 with VBA

Posted on 2011-02-28
11
395 Views
Last Modified: 2012-05-11
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
Comment
Question by:Massimo Scola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34996447
Show me the Workbook Open event?

Are you recreating the control there?

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34996459
You presumably added the commandbar with a appliaction.commandbars.add command and if so then try:

Application.CommandBars("My Macros").Delete

Chris
0
 

Author Comment

by:Massimo Scola
ID: 34997563
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997665
There is one line missing after line 5?

cmbControl.Caption = "My Macros"

Now try it.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34997730
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
 

Author Comment

by:Massimo Scola
ID: 34997845
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 350 total points
ID: 34997957
That'll be:

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

and delte that new line.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34997985
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
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 150 total points
ID: 34998128
Chris is right absolutely.

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

Sid
0
 

Author Comment

by:Massimo Scola
ID: 35004918
ah... right!
Thanks a lot!

Massimo
0
 

Author Closing Comment

by:Massimo Scola
ID: 35004928
thanks
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question