removing a menu item on closing

[Hammer_757]

Im learning about creating menu items and grabbing information from
few books and online.

If I create a floating toolbar using the following the toolbar will b
temporary and not be there the next time the workbook is opened.

Set cbar = Application.CommandBars.Add("Custom Toolbar"
msoBarFloating, False, True)
cbar.Visible = True

following this format:

expression.Add(Name, Position, MenuBar, Temporary) with "temporary" se
to TRUE


Creating a Custom menu the sample I am working with does not provid
the "temporary" attribute. Even though the ADD method is used.

Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup)
cbpop.Caption = "&custom"
cbpop.Visible = True

I think its because its a "control" so the add method is different.

Is there a way to make this temporary?
Thank

 

[Hammer_757]

A soon as I made the post I found it :O

the format is :
expression.Add(Type, Id, Parameter, Before, Temporary)

I used:
Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)

and it works great.

NEXT PROBLEM

I started fooling around with the [Befor] variant and cant seem to ge
that right. Excel Help says "A number that indicates the position o
the new control on the command bar. The new control will be inserte
before the control at this position. "

So I want mthe new menu to land befor the help menu Ive tried severa
versions of:
Controls.Add(Type:=msoControlPopup, Before:="Help", Temporary:=True)

but keep getting errors

 

[Tom Ogilvy]

As you quote:

A **number** that indicates the position of
the new control on the command bar

so you need to find the number of the help menu.

 

[Ron de Bruin]

so you need to find the number of the help menu.

30010 is the Help menu

--
Regards Ron de Bruin
http://www.rondebruin.nl

As you quote:

A **number** that indicates the position of
the new control on the command bar

so you need to find the number of the help menu.

--
Regards,
Tom Ogilvy

A soon as I made the post I found it :O

the format is :
expression.Add(Type, Id, Parameter, Before, Temporary)

I used:
Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)

and it works great.

NEXT PROBLEM

I started fooling around with the [Befor] variant and cant seem to get
that right. Excel Help says "A number that indicates the position of
the new control on the command bar. The new control will be inserted
before the control at this position. "

So I want mthe new menu to land befor the help menu Ive tried several
versions of:
Controls.Add(Type:=msoControlPopup, Before:="Help", Temporary:=True)

but keep getting errors.

 

[Jon Peltier]

Hammer -

"Help" isn't a number.

Set iBefore as Integer
iBefore = Application.CommandBars("Worksheet Menu Bar") _
.Controls("Help").Index
Set cbpop = Application.CommandBars("Worksheet Menu Bar") _
Controls.Add(Type:=msoControlPopup, Before:=iBefore, _
Temporary:=True)

- Jon

 

[Tom Ogilvy]

not the ID, the number of the control on the worksheet menu bar. The
controls index number

? commandbars("Worksheet Menu Bar").Controls(10).Caption
&Help

so on my menu, it would be 10

Note that on my menu bar there is a hidden button in position 8.

? commandbars("Worksheet Menu Bar").Controls(8).Caption
A&ction

Think this has been discussed before, but I don't recall the explanation.

--
Regards,
Tom Ogilvy

so you need to find the number of the help menu.

30010 is the Help menu

--
Regards Ron de Bruin
http://www.rondebruin.nl

As you quote:

A **number** that indicates the position of
the new control on the command bar

so you need to find the number of the help menu.

--
Regards,
Tom Ogilvy

A soon as I made the post I found it :O

the format is :
expression.Add(Type, Id, Parameter, Before, Temporary)

I used:
Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)

and it works great.

NEXT PROBLEM

I started fooling around with the [Befor] variant and cant seem to get
that right. Excel Help says "A number that indicates the position of
the new control on the command bar. The new control will be inserted
before the control at this position. "

So I want mthe new menu to land befor the help menu Ive tried several
versions of:
Controls.Add(Type:=msoControlPopup, Before:="Help", Temporary:=True)

but keep getting errors.

 

[Ron de Bruin]

Hi Tom

Part of the Excel help about that Item

Lotus NotesFlow is a set of workflow features that allows transparent
interaction between a Lotus Notes database and other programs such as
Microsoft Excel, Word, and PowerPoint. You can use NotesFlow to route forms,
send reminders, request and process approvals, and run scheduled batch
processes.

When Excel is participating in NotesFlow, an Action menu appears to the left
of the Excel Window menu. The Action menu displays each of the available
actions as commands. The commands available on the Action menu depend on how
the Lotus Notes form is designed. When you design a form in Lotus Notes to
use the NotesFlow feature, you publish the actions you want to make
available in other programs that support NotesFlow.

The Lotus NotesFlow features require Lotus Notes 4.0 or later. For more
information about how to use Lotus NotesFlow, refer to your Lotus Notes
documentation.


--
Regards Ron de Bruin
http://www.rondebruin.nl

not the ID, the number of the control on the worksheet menu bar. The
controls index number

? commandbars("Worksheet Menu Bar").Controls(10).Caption
&Help

so on my menu, it would be 10

Note that on my menu bar there is a hidden button in position 8.

? commandbars("Worksheet Menu Bar").Controls(8).Caption
A&ction

Think this has been discussed before, but I don't recall the explanation.

--
Regards,
Tom Ogilvy

so you need to find the number of the help menu.

30010 is the Help menu

--
Regards Ron de Bruin
http://www.rondebruin.nl

As you quote:

A **number** that indicates the position of
the new control on the command bar

so you need to find the number of the help menu.

--
Regards,
Tom Ogilvy


A soon as I made the post I found it :O

the format is :
expression.Add(Type, Id, Parameter, Before, Temporary)

I used:
Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)

and it works great.

NEXT PROBLEM

I started fooling around with the [Befor] variant and cant seem to get
that right. Excel Help says "A number that indicates the position of
the new control on the command bar. The new control will be inserted
before the control at this position. "

So I want mthe new menu to land befor the help menu Ive tried several
versions of:
Controls.Add(Type:=msoControlPopup, Before:="Help", Temporary:=True)

but keep getting errors.

 

[Ron de Bruin]

The OP can use this number

MsgBox Application.CommandBars(1).FindControl(, 30010).Index


--
Regards Ron de Bruin
http://www.rondebruin.nl

not the ID, the number of the control on the worksheet menu bar. The
controls index number

? commandbars("Worksheet Menu Bar").Controls(10).Caption
&Help

so on my menu, it would be 10

Note that on my menu bar there is a hidden button in position 8.

? commandbars("Worksheet Menu Bar").Controls(8).Caption
A&ction

Think this has been discussed before, but I don't recall the explanation.

--
Regards,
Tom Ogilvy

so you need to find the number of the help menu.

30010 is the Help menu

--
Regards Ron de Bruin
http://www.rondebruin.nl

As you quote:

A **number** that indicates the position of
the new control on the command bar

so you need to find the number of the help menu.

--
Regards,
Tom Ogilvy


A soon as I made the post I found it :O

the format is :
expression.Add(Type, Id, Parameter, Before, Temporary)

I used:
Set cbpop = Application.CommandBars("worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup, Temporary:=True)

and it works great.

NEXT PROBLEM

I started fooling around with the [Befor] variant and cant seem to get
that right. Excel Help says "A number that indicates the position of
the new control on the command bar. The new control will be inserted
before the control at this position. "

So I want mthe new menu to land befor the help menu Ive tried several
versions of:
Controls.Add(Type:=msoControlPopup, Before:="Help", Temporary:=True)

but keep getting errors.

 

[Hammer_757]

I cant believe I quoted "a number" and then tried to us text!

Thanks guy