Create toolbar with VB (notVBA)?
 

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

Create toolbar with VB (notVBA)?
 

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.

--

HTH

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

"Ed" wrote in message
...
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

Create toolbar with VB (notVBA)?
 

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" wrote in message
...
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.

--

HTH

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

"Ed" wrote in message
...
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

Create toolbar with VB (notVBA)?
 

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" wrote in message
...
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" wrote in message
...
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.

--

HTH

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

"Ed" wrote in message
...
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

Create toolbar with VB (notVBA)?
 

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

Ed

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

--

HTH

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

"Ed" wrote in message
...
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

Create toolbar with VB (notVBA)?
 

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.

Create toolbar with VB (notVBA)?
 

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

Ed

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

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.