Menu Bar in Excel 1

[JohnAcc]

Hi All,

Does anyone know how I can create a sub menu off a custom menu in excel ?

Similar to File > Print Area > Set Print Area

Here is what I have at the moment......

Private Sub Worksheet_Activate()

Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer

Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex, Temporary:=True)
With muCustom
.Caption = "&Promise Finance Options"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Month Lookup"
.BeginGroup = True
.OnAction = "Monthlookup"
End With
End With
End Sub

Thanx in advance for any help.

Rgds, John

 

[TonyJollans]

Hi John,

I thought there was a FAQ on this but just had a quick look and couldn't see it.

Change the Control you add to your custom menu to a popup and it will have its own Controls collection to which you can add further controls, something like ..

[blue]    With muCustom
        .Caption = "&Promise Finance Options"
        With .Controls.Add(Type:=msoControl[red]Popup[/red])
            .Caption = "&Month Lookup"
            .BeginGroup = True
            [red]With .Controls.Add(Type:=msoControlButton)
                .Caption = "&Month Lookup"
                .BeginGroup = True
                .OnAction = "Monthlookup"
            End With[/red]
        End With
    End With[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[JohnAcc]

Cheers Tony !

A star is on it's way.

Rgds, John