Solved

Delete commandbar in Excel 2003 with VBA

Posted on 2011-02-28
11
380 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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