Can anyone help me? I am trying to get a custom toolbar to load when I load
a specific workbook in Excel. The custom toolbar is designed and working,
but I want to autoload it when I load the specific workbook that it is used
for. What are the VB code lines that will do this?

Also I would like to know how to close (exit) the tool bar when I close the
specific workbook.

Thank you for your help.

Jim

Jim,

Here is a simple example. Put the code in Thisworkbook

Dim sToolbar As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sToolbar = "MyToolbar"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sTo olbar).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar
Dim oCtl As CommandBarControl
Dim newMenu As Object 'CommandBarControl
Dim ctrlButton As Object 'CommandBarControl

sToolbar = "MyToolbar"

On Error Resume Next
Application.CommandBars(sToolbar).Delete
On Error GoTo 0

Set oCB = Application.CommandBars.Add(temporary:=True)

With oCB
.Name = sToolbar
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "Margin Calculator"
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With
.Visible = True
End With

End Sub

--

HTH

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

"Jim Carter" wrote in message
...
Can anyone help me? I am trying to get a custom toolbar to load when I
load
a specific workbook in Excel. The custom toolbar is designed and working,
but I want to autoload it when I load the specific workbook that it is
used
for. What are the VB code lines that will do this?

Also I would like to know how to close (exit) the tool bar when I close
the
specific workbook.

Thank you for your help.

Jim

Jim, the easiest solution is to attach your toolbar to the workbook via
View, Toolbars, Customize. Then, when you close the workbook, use the
following macro that runs when a workbook is closed:

Sub Auto_Close()
commandbars("my toolbar name").delete
End Sub

You could hide the toolbar instead of deleting, but you would need to then
use a statement in the subrouting auto_open to unhide it. For example

commandbars("my toolbar name").visible = true or false

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel



"Jim Carter" wrote in message
...
Can anyone help me? I am trying to get a custom toolbar to load when I
load
a specific workbook in Excel. The custom toolbar is designed and working,
but I want to autoload it when I load the specific workbook that it is
used
for. What are the VB code lines that will do this?

Also I would like to know how to close (exit) the tool bar when I close
the
specific workbook.

Thank you for your help.

Jim

Jim

Try this KB Article.

http://msdn.microsoft.com/library/de...l/ofcmdbar.asp

If no happiness, post back.

Gord Dibben Excel MVP

On Mon, 16 Aug 2004 15:44:45 -0600, "Jim Carter" wrote:

Can anyone help me? I am trying to get a custom toolbar to load when I load
a specific workbook in Excel. The custom toolbar is designed and working,
but I want to autoload it when I load the specific workbook that it is used
for. What are the VB code lines that will do this?

Also I would like to know how to close (exit) the tool bar when I close the
specific workbook.

Thank you for your help.

Jim