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
Else
Set NewMenuItem = ToolsMenu.Controls.Add _
(Type:=msoControlButton)
With NewMenuItem
.Caption = "Supersum"
.OnAction = "loadsum"
End With
End If

End Sub

Sub DeleteMenuItem()
On Error Resume Next
CommandBars(1).FindControl(ID:=30007). _
Controls("Supersum").Delete
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
done.

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") _
.Controls.Add
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.

Adding a Custom menu / sub menu
 

Paul This is what I have used for Adding Custom Menu / sub menu
http://j-walk.com/ss/excel/tips/tip53.htm
"PaulW" wrote:

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
Else
Set NewMenuItem = ToolsMenu.Controls.Add _
(Type:=msoControlButton)
With NewMenuItem
.Caption = "Supersum"
.OnAction = "loadsum"
End With
End If

End Sub

Sub DeleteMenuItem()
On Error Resume Next
CommandBars(1).FindControl(ID:=30007). _
Controls("Supersum").Delete
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
done.

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") _
.Controls.Add
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.

Adding a Custom menu / sub menu
 

"Mike" wrote:

Paul This is what I have used for Adding Custom Menu / sub menu
http://j-walk.com/ss/excel/tips/tip53.htm
"PaulW" wrote:

I was hoping that I could modify it so that instead of looking at cells I
could hard code it. But I was slightly worried that the "Download
menumakr.xls" link actually links to an .exe file not a spreadsheet...

Adding a Custom menu / sub menu
 

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.

"PaulW" wrote:



"Mike" wrote:

Paul This is what I have used for Adding Custom Menu / sub menu
http://j-walk.com/ss/excel/tips/tip53.htm
"PaulW" wrote:

I was hoping that I could modify it so that instead of looking at cells I
could hard code it. But I was slightly worried that the "Download
menumakr.xls" link actually links to an .exe file not a spreadsheet...

Adding a Custom menu / sub menu
 

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.

"Mike" wrote:

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.

"PaulW" wrote:



"Mike" wrote:

Paul This is what I have used for Adding Custom Menu / sub menu
http://j-walk.com/ss/excel/tips/tip53.htm
"PaulW" wrote:

I was hoping that I could modify it so that instead of looking at cells I
could hard code it. But I was slightly worried that the "Download
menumakr.xls" link actually links to an .exe file not a spreadsheet...

Adding a Custom menu / sub menu
 

PaulW,

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

Regards,

Sanj


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

"Mike" wrote:

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.

"PaulW" wrote:



"Mike" wrote:

Paul This is what I have used for Adding Custom Menu / sub menu
http://j-walk.com/ss/excel/tips/tip53.htm
"PaulW" wrote:

I was hoping that I could modify it so that instead of looking at cells
I
could hard code it. But I was slightly worried that the "Download
menumakr.xls" link actually links to an .exe file not a spreadsheet...