sub menu

Hi all,

i would like to create sub menu which created automatically when i open up the files(on any computer). So far i successful creating main menu but have no success on sub menu. My main menu look like this:

this workbook.

Dim cmbBar As CommandBar
Dim cmbControl As CommandBarControl

Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup, temporary:=True) 'adds a menu item to the Menu Bar
With cmbControl
.Caption = "New quick estimate 2010" 'names the menu item
With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
.Caption = "Access holes" 'adds a description to the menu item
.OnAction = "estimateaccessholeformmacro" 'runs the specified macro
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Anchor&Chain"
.OnAction = "anchorandchainlinkformmacro"

End With



anybody can guide me on adding the sub menu??

Another thing how can i add the short cut in the right click menu which can work in any computer?

thanks in advance.

What do you mean by created automatically? Do you mean every file opened adds a new entry into the sub-menu, and removes it on close.

To add to right clisk, use commandbars("Cell").

created automatically mean that the menu will stay where ever i open up the excel file. it will appear when i open it at my pc and will also appear when i open at my laptop with req i open up that file. later on i thinking of adding the userform to that sub menu just like what i do on my code up there to the main menu.

can you be more specific on the "cell" function if it is what i looking for? example code or upload it will be appreciated.

To get it to stay with the workbook, generate the menu/sub-menu when you opne the workbook, using the worrkbook open event.

For cell, something like


Public Sub addMenu()
removeMenu 'just in case
With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Routine1"
.OnAction = "Another"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Routine2"
.OnAction = "YetMore"
End With
End With
End Sub


Public Sub removeMenu()
On Error Resume Next
With Application.CommandBars("Cell")
.Controls("Routine1").Delete
.Controls("Routine2").Delete
End With
On Error GoTo 0
End Sub


Again, initiate it from the workbook open.

Thanks you giving me new knowledge.

However the one i looking for is like in the picture.

see the auto filter menu.

it is not in the main menu instead in the sub menu.

can you help?

and do you know how to add in menu in the right click menu?

Hi,

I have a similar problem and is also intrested in this solution.:)
Is it correct that you need to now how do you address the "sub toolbar" to the "original" one?

In order to have the toolbar objekt created every time you open the file you can use "(Sub Auto_open()" or "Sub Workbook_Open()".
As an alternative, depending on what you are looking for, I've just learned that you can also save your workbook as .xla (xl2003 and earlier) and store it in the xlstart folder on each pc. Then it starts every time excel starts, like an addin.

My issue is how to address a menyoption to a hyperlink in VBA-code. Any ideas here would be greatly apprechiated. Can it be done or not? :p
Example would be something like this:

With .Controls.Add(Type:=msoControlButton)
.Caption = "Routine2"
.OnAction = "C:\myfile.xls"
End With

Thanks you giving me new knowledge.

However the one i looking for is like in the picture.

see the auto filter menu.

it is not in the main menu instead in the sub menu.

can you help?

You just nest the code one more level, setting the type to msoControlPopup instead of msoControlButton (that tells VBA that there are lower levels), and then add another control under that.

and do you know how to add in menu in the right click menu?[/QUOTE]

That would be the same approach as above.

BTW, I see you have those horrible restricted menus that only show recently used menu options, how can you tolerate that, it is a hideous function?

Thank you bob. It what i looking for and it is working. however can you paste the code for right click menu and where to put those code? i has no clue for this.

for my menu, i not a programmer therefore i dont know any other way but from my opinion those menu met my objective. it is not to appear every time but only when i need it by opening certain file. thank you again.