Adding a Custom menu / sub menu



Sub CreateMenuItem()
Dim ToolsMenu As CommandBarPopup
Dim NewMenuItem As CommandBarButton

Call DeleteMenuItem

Set ToolsMenu = CommandBars(1).FindControl(ID:=30007)
If ToolsMenu Is Nothing Then
MsgBox "Cannot add Supersum item to Tools menu."
Exit Sub
Set NewMenuItem = ToolsMenu.Controls.Add _
With NewMenuItem
.Caption = "Supersum"
.OnAction = "loadsum"
End With
End If

End Sub

Sub DeleteMenuItem()
On Error Resume Next
CommandBars(1).FindControl(ID:=30007). _
End Sub

Sub loadsum()
updater.Show (0)
End Sub


Thats what I have, and it's pretty much copied and pasted 3 times with the
odd word changed so that the other userforms i've created work as well. The
other userforms are calls Manginfo and Prodmon.

As I make more Userforms for people to use the tools menu is getting full of
stuff, so I could do with making a submenu in the tools to show these three
to make it less crowded, and easier for people to spot the extra things i've

To my eternal shame, i've found the answer on this forum, but since I don't
really understand it i've been unable to modify the code to get it to work.

Sub Allen()
Dim Item As CommandBarControl
Set Item = CommandBars(1).Controls("Michigan154") _
With Item
.Caption = "&Allen Park"
.OnAction = "AllenPark_154"
With .Controls.Add(Type:=msoControlPopup)
.Caption = "SubMenu"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sub Item 1"
.OnAction = "myMacro1"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Sub Item 2"
.OnAction = "myMacro2"
End With
End With
End With
End Sub

Is the shortest of the ones I found, and it looks easy enough to add new
subitems with macros as and when needed, but like I said, I couldn't figure
out what I need to change to make it work...

It errored on the set item line, so I changed the controls to tools, and it
got as far as with .controls.add(type:=msocontrolbutton) line and errored
again, and I couldn't figure out how to sort it.




No its not an .exe file its a sheet with in your Spreedsheet and once
you are down adding to it you can make the sheet hidden. Then if
you need to modify you just unhide the sheet and add to it.
This really does work great.


When I hover the mouse above the link it tells me in the info bar at the
bottom that the file is an .exe file. When I click on the link the download
status box says the "Filename:" is an .exe. Finally, if I choose open rather
than download a security warning comes up asking if i'm sure I want to instal
and run this program.





Don't worry its an self executable zip file - I've downloaded it and there's
no viruses etc.




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