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.

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.

"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...

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...

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...

PaulW,

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

Regards,

Sanj


"PaulW" <(E-Mail Removed)> wrote in message
newsECF83B8-100A-4744-B9F1-(E-Mail Removed)...
> 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...