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