Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu-Item and worksheet deletion questions
Hi,
I opened a THIRD person's workbook and it built a menu-bar with the code pasted below. Now, once I closed the workbook the menu-bar still remains. How do I remove it permanently (I can hide it by doing right click and then unchecking the check mark against the item "insert worksheet" but I dont want it to be present even there). I have tried restarting Excel XP/Windows XP SP2 but to of no avail. Also, something strange. If I delete a worksheet then one would get the message similar to "If u delete this worksheet then it will no longer be available" and so on. But the sheet inserted using the code below (by clicking on the graphic worksheet icon based on the code below) and when I subsequently delete it then no message is displayed. But again this behaviour itself if erratic. If I insert a new sheet manually (sheet 1) and then another new sheet (sheet 2) by clicking on the above icon then if i try to delete sheet 1 and then sheet2 then I start getting messages for BOTH cases. It's like as if Excel suddenly wakes up!! for that excel session. But if I again start a new excel session then I can insert sheets using the menu-item code and then delete it at will without invoking the messages. Whats going on here? Please guide me. Thanks a lot, Hari India Sub NEWMENUITEM() Set myMenuBar = CommandBars.ActiveMenuBar Set NEWMENU = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=False) NEWMENU.Caption = "INSERT WORKSHEETS" Set ctrl1 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl1 .Caption = "Table" .TooltipText = "Insert Table Worksheet" .Style = msoButtonCaption .OnAction = "NewTable" End With Set ctrl2 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl2 .Caption = "Bar" .TooltipText = "Insert Bar Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewBar" End With Set ctrl3 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl3 .Caption = "Column" .TooltipText = "Insert Column Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewColumn" End With Set ctrl4 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl4 .Caption = "Stacked" .TooltipText = "Insert Stacked Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewStacked" End With Set ctrl5 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl5 .Caption = "Gnatt" .TooltipText = "Insert Gnatt Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewGnatt" End With Set ctrl6 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl6 .Caption = "PIE" .TooltipText = "Insert Pie Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewPie" End With Set ctrl7 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl7 .Caption = "Scatter" .TooltipText = "Insert Scatter Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewScatter" End With Set ctrl8 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl8 .Caption = "IMP/PERF" .TooltipText = "Insert Importance/Performance Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewIMPPERF" End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu-Item and worksheet deletion questions
Hari,
To remove the menu goto Tools:Custumize...:Toolbars and select "Worksheet Menu bar" then click the Reset button. This will reset the menu to its original state. As for you missing message. Without the code for the OnAction macros I can't see the reason but I presume the macro that creates the sheet also disables the alerts display. This is done by setting Application.DisplayAlerts = false. Fred "Hari Prasadh" wrote in message ... Hi, I opened a THIRD person's workbook and it built a menu-bar with the code pasted below. Now, once I closed the workbook the menu-bar still remains. How do I remove it permanently (I can hide it by doing right click and then unchecking the check mark against the item "insert worksheet" but I dont want it to be present even there). I have tried restarting Excel XP/Windows XP SP2 but to of no avail. Also, something strange. If I delete a worksheet then one would get the message similar to "If u delete this worksheet then it will no longer be available" and so on. But the sheet inserted using the code below (by clicking on the graphic worksheet icon based on the code below) and when I subsequently delete it then no message is displayed. But again this behaviour itself if erratic. If I insert a new sheet manually (sheet 1) and then another new sheet (sheet 2) by clicking on the above icon then if i try to delete sheet 1 and then sheet2 then I start getting messages for BOTH cases. It's like as if Excel suddenly wakes up!! for that excel session. But if I again start a new excel session then I can insert sheets using the menu-item code and then delete it at will without invoking the messages. Whats going on here? Please guide me. Thanks a lot, Hari India Sub NEWMENUITEM() Set myMenuBar = CommandBars.ActiveMenuBar Set NEWMENU = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=False) NEWMENU.Caption = "INSERT WORKSHEETS" Set ctrl1 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl1 .Caption = "Table" .TooltipText = "Insert Table Worksheet" .Style = msoButtonCaption .OnAction = "NewTable" End With Set ctrl2 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl2 .Caption = "Bar" .TooltipText = "Insert Bar Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewBar" End With Set ctrl3 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl3 .Caption = "Column" .TooltipText = "Insert Column Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewColumn" End With Set ctrl4 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl4 .Caption = "Stacked" .TooltipText = "Insert Stacked Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewStacked" End With Set ctrl5 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl5 .Caption = "Gnatt" .TooltipText = "Insert Gnatt Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewGnatt" End With Set ctrl6 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl6 .Caption = "PIE" .TooltipText = "Insert Pie Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewPie" End With Set ctrl7 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl7 .Caption = "Scatter" .TooltipText = "Insert Scatter Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewScatter" End With Set ctrl8 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl8 .Caption = "IMP/PERF" .TooltipText = "Insert Importance/Performance Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewIMPPERF" End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu-Item and worksheet deletion questions
If the menu was "built" properly, then this should do it:
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 If not, perhaps you should fix the code. http://www.vbaexpress.com/kb/getarticle.php?kb_id=427 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Hari Prasadh" wrote in message ... Hi, I opened a THIRD person's workbook and it built a menu-bar with the code pasted below. Now, once I closed the workbook the menu-bar still remains. How do I remove it permanently (I can hide it by doing right click and then unchecking the check mark against the item "insert worksheet" but I dont want it to be present even there). I have tried restarting Excel XP/Windows XP SP2 but to of no avail. Also, something strange. If I delete a worksheet then one would get the message similar to "If u delete this worksheet then it will no longer be available" and so on. But the sheet inserted using the code below (by clicking on the graphic worksheet icon based on the code below) and when I subsequently delete it then no message is displayed. But again this behaviour itself if erratic. If I insert a new sheet manually (sheet 1) and then another new sheet (sheet 2) by clicking on the above icon then if i try to delete sheet 1 and then sheet2 then I start getting messages for BOTH cases. It's like as if Excel suddenly wakes up!! for that excel session. But if I again start a new excel session then I can insert sheets using the menu-item code and then delete it at will without invoking the messages. Whats going on here? Please guide me. Thanks a lot, Hari India Sub NEWMENUITEM() Set myMenuBar = CommandBars.ActiveMenuBar Set NEWMENU = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=False) NEWMENU.Caption = "INSERT WORKSHEETS" Set ctrl1 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl1 .Caption = "Table" .TooltipText = "Insert Table Worksheet" .Style = msoButtonCaption .OnAction = "NewTable" End With Set ctrl2 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl2 .Caption = "Bar" .TooltipText = "Insert Bar Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewBar" End With Set ctrl3 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl3 .Caption = "Column" .TooltipText = "Insert Column Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewColumn" End With Set ctrl4 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl4 .Caption = "Stacked" .TooltipText = "Insert Stacked Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewStacked" End With Set ctrl5 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl5 .Caption = "Gnatt" .TooltipText = "Insert Gnatt Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewGnatt" End With Set ctrl6 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl6 .Caption = "PIE" .TooltipText = "Insert Pie Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewPie" End With Set ctrl7 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl7 .Caption = "Scatter" .TooltipText = "Insert Scatter Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewScatter" End With Set ctrl8 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl8 .Caption = "IMP/PERF" .TooltipText = "Insert Importance/Performance Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewIMPPERF" End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Menu-Item and worksheet deletion questions
The code you posted adds a new menu ("INSERT WORKSHEETS") to an existing menu
bar and adds menu items to this new menu. It does not by itself create a new menu bar (e.g. the Worksheet Menu Bar). The code also attempts to give the menu items tool tips which they do not support. So the TooltipText code is useless. I assume there is additional code that creates the new menu bar that you refer to. As for the worksheet deletion issue, this is atypical and likely resides with the macro code responsible for their creation, which you have not posted. For what it's worth, a suggested rewrite of the code that creates the new menu and menu items follows. This will not resolve any of the issues you mentioned. It is only a condensation of the posted code. You should enter an appropriate tool tip for the new menu in place of the question marks ("?????"). Regards, Greg Sub MakeNewMenu() Dim myMenuBar As CommandBar Dim NewMenu As CommandBarControl Dim ctrl As CommandBarControl Dim Arr1 As Variant, Arr2 As Variant Dim i As Integer Arr1 = Array("Table", "Bar", "Column", "Stacked", _ "Gnatt", "Pie", "Scatter", "IMP/PERF") Arr2 = Array("NewTable", "NewBar", "NewColumn", _ "NewStacked", "NewGnatt", "NewPie", "NewScatter", _ "NewIMPPERF") Set myMenuBar = Application.CommandBars.ActiveMenuBar Set NewMenu = myMenuBar.Controls.Add(msoControlPopup) NewMenu.Caption = "Insert Worksheets" NewMenu.TooltipText = "?????" For i = 0 To 7 Set ctrl = NewMenu.Controls.Add With ctrl .Caption = Arr1(i) .Style = msoButtonCaption .OnAction = Arr2(i) End With Next End Sub "Hari Prasadh" wrote: Hi, I opened a THIRD person's workbook and it built a menu-bar with the code pasted below. Now, once I closed the workbook the menu-bar still remains. How do I remove it permanently (I can hide it by doing right click and then unchecking the check mark against the item "insert worksheet" but I dont want it to be present even there). I have tried restarting Excel XP/Windows XP SP2 but to of no avail. Also, something strange. If I delete a worksheet then one would get the message similar to "If u delete this worksheet then it will no longer be available" and so on. But the sheet inserted using the code below (by clicking on the graphic worksheet icon based on the code below) and when I subsequently delete it then no message is displayed. But again this behaviour itself if erratic. If I insert a new sheet manually (sheet 1) and then another new sheet (sheet 2) by clicking on the above icon then if i try to delete sheet 1 and then sheet2 then I start getting messages for BOTH cases. It's like as if Excel suddenly wakes up!! for that excel session. But if I again start a new excel session then I can insert sheets using the menu-item code and then delete it at will without invoking the messages. Whats going on here? Please guide me. Thanks a lot, Hari India Sub NEWMENUITEM() Set myMenuBar = CommandBars.ActiveMenuBar Set NEWMENU = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=False) NEWMENU.Caption = "INSERT WORKSHEETS" Set ctrl1 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl1 .Caption = "Table" .TooltipText = "Insert Table Worksheet" .Style = msoButtonCaption .OnAction = "NewTable" End With Set ctrl2 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl2 .Caption = "Bar" .TooltipText = "Insert Bar Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewBar" End With Set ctrl3 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl3 .Caption = "Column" .TooltipText = "Insert Column Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewColumn" End With Set ctrl4 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl4 .Caption = "Stacked" .TooltipText = "Insert Stacked Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewStacked" End With Set ctrl5 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl5 .Caption = "Gnatt" .TooltipText = "Insert Gnatt Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewGnatt" End With Set ctrl6 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl6 .Caption = "PIE" .TooltipText = "Insert Pie Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewPie" End With Set ctrl7 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl7 .Caption = "Scatter" .TooltipText = "Insert Scatter Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewScatter" End With Set ctrl8 = NEWMENU.CommandBar.Controls _ .Add(Type:=msoControlButton, ID:=1) With ctrl8 .Caption = "IMP/PERF" .TooltipText = "Insert Importance/Performance Chart Worksheet" .Style = msoButtonCaption .OnAction = "NewIMPPERF" End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keep new menu item with workbook | Excel Discussion (Misc queries) | |||
Menu Item name | Excel Programming | |||
Menu items added with menu item editor in older versions | Excel Discussion (Misc queries) | |||
Adding a menu item right click menu when clicking on a single. | Excel Programming | |||
New menu bar item | Excel Programming |