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
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
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