Create toolbar with VB (notVBA)?

[Ed]

Due to some conflicts (L-O-N-G story!), I am now trying to use a Visual
Basic app to open an Excel workbook across a network. The workbook
originally had several search functions coded into it; these now must go
into the VB app. The difficulty is allowing my users to access them. I can
either create a floating toolbar with a VB form, or create a custom toolbar
in Excel when the workbook is opened.

I've found several examples on creating the toolbar in Excel with VBA, and
I've been told I can manipulate the Excel objects and methods by VB. So -
theoretically, it should be simple to create this toolbar on the fly when
the workbook opens on the user's system, and delete it when they are done,
right?

Am I jumping into deep waters without my floaties?

Ed

 

[Bob Phillips]

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").Delete
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.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ed]

Thank you, Bob. (Boy, I'll be glad when *I* can "just knock up a bit of
code" like that!)

One question: I already have the code on a button that calls my Excel file
(copied below). I assume that I would put this in the same module. But I
notice you do this with the xlApp, and not with the specific file. So
should this go before I set a reference to the file and open it? I don't
want the user to be stuck with a toolbar on their application after I'm
done!

Ed

Private Sub btnShowExcel_Click()

Dim objXL As Object
Dim objWkbk As Object
Dim strFile As String
Dim strFPath As String
Dim strShell As String
Dim nmbFPChr As Long

strFPath = App.Path
nmbFPChr = Len(strFPath)
nmbFPChr = (nmbFPChr - 8)
strFPath = Left(strFPath, nmbFPChr)
strFile = Dir(strFPath & "*.xls")
strShell = strFPath & strFile

Set objXL = CreateObject("Excel.Application")
Set objWkbk = objXL.Workbooks.Open(strShell)
Form1.Visible = False
objXL.Visible = True
Form3.Visible = True

End Sub

 

[Bob Phillips]

Ed,

It doesn't really matter, before or after. Commandbars are part of Excel
application, that is why it is referenced vi the xlApp object (or objXL in
your case).

If you also notice I set the temporary property of the commandbar control,
so it won't hang around. You could also replicate that delete code that I
included at the start of the routine.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ed]

Thank you, Bob. I will play with this and holler if I have problems.

Ed

 

[Steve Garman]

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

 

[Ed]

Thank you, Steve. One less call for help! <g>

Ed

 

[Bob Phillips]

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)