Thank you Steve. I did say I did it from VBA <vbg
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve Garman" wrote in message
...
Just one small point about using Bob's code in VB
There are 3 constants VB won't recognize
msoControlPopup = 10
msoControlButton = 1
msoButtonCaption = 2
Just replace them with the appropriate number or define your own constants
Bob Phillips wrote:
Ed,
I just knocked up this bit of code and ran it okay. Admittedly, it was
in
VBA, but it uses automation so it will work just as well in VB
Sub myMenu()
Dim oCB As Object
Dim oCtl As Object 'CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl
Dim xlApp As Object
Dim sh As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
On Error Resume Next
xlApp.CommandBars("Worksheet Menu
Bar").Controls("Tools").Controls("Bars2004").Delet e
On Error GoTo 0
Set oCB = xlApp.CommandBars("Worksheet Menu Bar")
Set oCtl = oCB.Controls("Tools")
Set newMenu = oCtl.Controls.Add(Type:=msoControlPopup,
temporary:=True)
With newMenu
.Caption = "myMenu"
Set ctrlButton = .Controls.Add(Type:=msoControlButton, ID:=1)
With ctrlButton
.Caption = "mySubMenu"
.Style = msoButtonCaption
.OnAction = "myMacro"
End With
End With
End Sub
This should give you a start.
You don't need to use late binding, you could just as easily set a
reference
to Excel, and use early binding, declaring the objects explicitly.