New Main menu item

C

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
 
B

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)
 
S

Simon Lloyd

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
 
J

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]
 
B

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 said:
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]

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
Click to expand...
Click to expand...
 
J

Jake Marx

Bob said:
temporary:=True
Click to expand...

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]
 
B

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 said:
Bob said:
temporary:=True
Click to expand...

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]
Click to expand...
 
C

Cynthia

Bob said:
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
Click to expand...
Thanks everyone...that works for me!

Cindi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

menu bar and error 91 3
Adding "New Comment" command to all existing and new files. 2
Excel 2013 menubar incompatibility? 1
Custom Menus and OnAction 4
Delete specific menu item from custom menu 2
Hot Key on Menu Bar 3
Nested Sub Menus 3
Workbook_open not executing 1

Top