Deleting menu bar in Excel 2007 using VBA

    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