Menu-Item and worksheet deletion questions

[Hari Prasadh]

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

 

[Fred]

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

 

[Anne Troy]

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

 

[Greg Wilson]

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