adding submenus to a drop down menu

[enfantter]

Hi,

I have used the following code in order to create a dropdown menu ...
Set myMainMenuBar = Application.CommandBars.ActiveMenuBar

Set myCustomMenu = myMainMenuBar.Controls.Add(Type:=msoControlPopup, _
before:=10) 'before menu 3
myCustomMenu.Caption = "SIA"

'New show
Set myTempMenu = myCustomMenu.Controls.Add(Type:=msoControlButton)
With myTempMenu
.Caption = "New graph"
.OnAction = "hentpopup" 'name of macro
End With

and now i would like to add a submenu to this drop down menu ....

something like this in excel
Data -> Filter -> autofilter

and is there also a way to add the little pictures which sometimes occur ??

[Bob Phillips]

With Application.CommandBars.ActiveMenuBar.Controls.Add(Type:=msoControlPopup, _
before:=10) 'before menu 3

.Caption = "SIA"

With .Controls.Add(Type:=msoControlButton)

.Caption = "New graph"
.OnAction = "hentpopup" 'name of macro
End With

With .Controls.Add(Type:=msoControlPopup)

.Caption = "Submenu"

With .Controls.Add(Type:=msoControlButton)

.Caption = "Some action"
.FaceId = 29
.OnAction = "SomeAction"
End With

With .Controls.Add(Type:=msoControlButton)

.Caption = "Some other action"
.FaceId = 30
.OnAction = "SomeOtherAction"
End With
End With
End With


See also http://j-walk.com/ss/excel/tips/tip40.htm

[enfantter]

Super - this is exactly what i need.
Is there any quick way to use a custom FaceId eg from a file path?!
(i have tried this but cannot manage to get it to work)

[Bob Phillips]

Not from a filepath, but you can embed the image on a sheet and use




cbTable.Shapes(shapename).CopyPicture
cbCtl.PasteFace


where cbTable is the codename of the sheet containing the picture, shapename is the name of the shape, such as 'Picture 30', and cbCtl is an object variable for the control being added.

[enfantter]

Ok - i guess this will work ..
But how do i call this picture..
Do i paste this directly eg
.FaceId = [your code]
or could i call it somehow?

[Bob Phillips]

No, that is the code to call it.

You embed the picture in a worksheet, give it a name (shapename), and use that code.

[enfantter]

Super - thanks a lot...
I have one further problem though - sorry for loading it all off on this thread, but...

when i activate a graph - my add dissappears - is this normal?!

[enfantter]

That is to have available something like the normal toolbar

[Bob Phillips]

You need to explain this phrase

... when i activate a graph - my add dissappears - is this normal?! ...

I have no idea what that means.

[enfantter]

Sorry ..
I'm talking about the toolbar. This changes when you activate a graph. ex Data changes to Chart and add-ins dissappear from the toolbar..

Does this make any sense ..

[Bob Phillips]

That is because the Chart Menu Bar is activated when a chart is selected. Add the menu to that commandbar as well.