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, befo="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

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" wrote in message
news:mQTmc.316$BJ6.21823@attbi_s51...
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,
befo="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

Hiya,

Well im new to all this but in some workbooks ive done i have turned
off the worksheet menu bars n tool bars like this (dont forget to put
them in the auto close to turn them back on....

Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Worksheet Menu Bar").Visible = False
Application.CommandBars.ActiveMenuBar.Enabled = False

The worksheet menu bar one turns off te standard menu bar, as you hae
probably found you can't turn it off by right clicking on the tool bar
choosing customise and uncheck the box there.

Hope this helps

Simon


---
Message posted from http://www.ExcelForum.com/

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]


Cynthia wrote:
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,
befo="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

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" wrote in message
...
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]


Cynthia wrote:
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,
befo="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 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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

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" wrote in message
...
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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Bob Phillips wrote:
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



Thanks everyone...that works for me!

Cindi