New Main menu item

[Cynthia]

I am trying to use this code I copied from a spreadsheet and changed using help from this newsgroup,
the commented line uses the "modern" version (but doesn't work at all. With the "Menubars"
collection, the Delete method always throws an error...though it can be trapped this is not right.
How can I add a new item to the main menu with 12 subitems?

strMenuName = "My new menu"

' Delete the menu if it already exists
MenuBars("Worksheet Menu Bar").Menus(strMenuName).Delete
'Application.CommandBars("Worksheet Menu Bar").Controls(strMenuName).Delete

' Add the main menu
MenuBars("Worksheet Menu Bar").Menus.Add Caption:=strMenuName, before:="Help"
'Application.CommandBars("Worksheet Menu Bar").Controls.Add.Caption = strMenuName

' Add the submenu items
With MenuBars("Worksheet Menu Bar").Menus(strMenuName).MenuItems
'With Application.CommandBars("Worksheet Menu Bar").Controls(strMenuName)

.Add Caption:="Save this worksheet", OnAction:="cmdSaveMe"
...blah, blah, blah...
End With

Thank you,

Cindi

[Bob Phillips]

Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim strMenuName As String

strMenuName = "My new menu"

Set oCB = Application.CommandBars("Worksheet Menu Bar")

On Error Resume Next

' Delete the menu if it already exists

oCB.Controls(strMenuName).Delete
On Error GoTo 0

' Add the main menu
Set oCtl = oCB.Controls.Add(Type:=msoControlPopup, temporary:=True)

' Add the submenu items
With oCtl
.Caption = strMenuName
With .Controls.Add(Type:=msoControlButton)
.Caption = "Save this worksheet"
.OnAction = "cmdSaveMe"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Hide this worksheet"
.OnAction = "cmdHideMe"
End With
'etc.
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Cynthia" <cbe...@nospam.net> wrote in message
news:mQTmc.316$BJ6.21823@attbi_s51...

[Jake Marx]

Hi Cynthia,

I typically do these types of things using With statements. So if I wanted
to create a new menu item named "Test Menu" with 3 submenus (or buttons),
then I would probably do something like this:

Public Sub CreateMenus()
DeleteMenus

With Application.CommandBars("Worksheet Menu Bar" _
).Controls.Add(Type:=msoControlPopup)
.Caption = "Test Menu"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Test 1"
.OnAction = "test1"
.Enabled = True

End With
With .Controls.Add(Type:=msoControlButton)

.Caption = "Test 2"
.OnAction = "test2"
.Enabled = False

End With
With .Controls.Add(Type:=msoControlButton)

.Caption = "Test 3"
.OnAction = "test3"
.Enabled = True
End With
End With
End Sub

Public Sub DeleteMenus()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar" _
).Controls("Test Menu").Delete
On Error GoTo 0
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

[Bob Phillips]

Jake,

temporary:=True

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jake Marx" <msn...@longhead.com> wrote in message
news:Od%23CoNIN...@TK2MSFTNGP11.phx.gbl...

[Jake Marx]

Bob Phillips wrote:
> temporary:=True

I typically do that, but I forgot in this case. Just a safety net in case
they don't get deleted for some reason or another. Thanks.

[Bob Phillips]

defensive programming I call it <vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jake Marx" <msn...@longhead.com> wrote in message

news:O3o5BkI...@TK2MSFTNGP11.phx.gbl...

[Cynthia]

Thanks everyone...that works for me!

Cindi