Solved

Delete commandbar in Excel 2003 with VBA

Posted on 2011-02-28
11
406 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot 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…

707 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