View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_6_] Bob Phillips[_6_] is offline
external usenet poster
 
Posts: 11,272
Default Create toolbar with VB (notVBA)?

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.