Menu item deletion

[Guest]

I have been working with Steven Roman's, 'Writing Excel Macros with VBA' 2nd
Edition. The code below adds the Custom popup menu to the worksheet menu. The
code does not contain a Temporary:=True statment when the control was added
to the menu. Now I alway have a Custom popup menu when I launch Excel.

Before I add the Temporary:=True to the Controls.Add line of code I want to
delete the existing Custom menu. Can I get some help on how I can ID the
current Custom menu item? No Tag was set and I don't know how to determine
the controls ID so I can use it for the purpose of deleting it.

Thanks in advance

Hal

Sub CreatePopup()
' Example 12-5 pg 149

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbsub As CommandBarControl

' Create a popup control on the main menu bar
Set cbpop = Application.CommandBars("Worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup)
cbpop.Caption = "&Custom"
cbpop.Visible = True
' Add a menu item
Set cbctl = cbpop.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
' Next is required for caption
cbctl.Style = msoButtonCaption
cbctl.Caption = "MenuItem&1"
' Action to perform
cbctl.OnAction = "ExampleMacro1"
' Add a popup for a submenu
Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
cbsub.Visible = True
cbsub.Caption = "&SubMenuItem1"
' Add a menu item to the submenu
Set cbctl = cbsub.Controls.Add(Type:=msoControlButton)
cbctl.Visible = True
' Next is required for caption
cbctl.Style = msoButtonCaption
cbctl.Caption = "SubMenuItem&2"
' Action to perform
cbctl.OnAction = "ExampleMacro2"

End Sub

 

[Dave Peterson]

You can delete the new control based on its caption.

Near the top of your code:

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&custom").Delete
On Error GoTo 0

One thing you may want to do before you do too much experimenting:
Close excel
Use windows start button|Search
to find your *.xlb file
This is where excel stores these customizations.

Make a copy of it somewhere.

Then if you screw up, er, when you screw up, you can just close excel and copy
that backup file into its real home.

It might save some tears later.

Alternatively, you can reset all customizations that you've made to the
worksheet menu bar:

Tools|Customize|toolbars tab
select worksheet menu bar
click reset

 

[Guest]

Thanks for your assistance Dave. I'll make that backup copy of my xlb file.
Like any new adventure, I'm bound to set Excel into an endless loop or some
other snafu that will be hard to recover from. But what's life without a
little risk now and then?

Hal Innes