Deleting menu bar in Excel 2007 using VBA

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Dear forum

    I am writing this code in excel 2007 VBA and have got stuck and therefore hoping for your help

    this below code generates a menu bar under "Add-In" and launches a userform upon action

    Code
    Sub commandbar_generation()
Set mymenubar = CommandBars.ActiveMenuBar
Set newMenu = mymenubar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "PLAY"
newMenu.OnAction = "Uform"
End Sub



    now I want this menu to be deleted whenever I switch into another excel file....ie this menu should be available only from that particular file..I checked up this post http://www.ozgrid.com/forum/sh…ete+commandbar+excel+2007
    and have written this code under

    Code
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CommandBars.FindControl(Tag:="mymenubar").Delete
End Sub



    but whenever the window gets deactivated I am getting a runtime error...I am not able to understand..

    ps the error comes even when I use Tag:="newmenu"

    can someone pl tell me where I am going wrong and how to deactivate the menu when the file is not in use

    thanx a million in advance

    Re: Deleting menu bar in Excel 2007 using VBA


    junho, it doesn't look like pangolin is modifying the xml.


    pangolin, hth


    Code
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
on error resume next
Application.CommandBars("Worksheet Menu Bar").Controls("PLAY").Delete
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
call commandbar_generation
End Sub

    Re: Deleting menu bar in Excel 2007 using VBA


    Hi
    For me this works.


    Regards, junho

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login