How can I add a new tool bar to the Excel using VBA.
One way tried is read the resource file and copy on clipboard and paste it
on command bar.
But when we open more than one instances of XL, tool bar is not visible in
all of them completely.

How can we handle this?

Thanks and rgds

Here is some sample code to create a toolbar button on the Formatting
toolbar. This code would go in the ThisWorkbok code module of the addin.

To create your own toolbar, just add

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

Set oCB = Application.CommandBars.add("myToolbar", temporary:=true)
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

in place of the code that gets the formatting CB. Change the delete in
BeforeClose as well

I would also add my usual corollary that to see what FaceIds are available,
visit John Walkenbach's site at http://j-walk.com/ss/excel/tips/tip67.htm

Option Explicit

Dim sMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

sMenu = "myButton"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMe nu).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

sMenu = "Margin Calculator"

On Error Resume Next
Application.CommandBars("Formatting").Controls(sMe nu).Delete
On Error GoTo 0

Set oCB = Application.CommandBars("Formatting")
Set oCtl = oCB.Controls.Add(Type:=msoControlButton, temporary:=True)

With oCtl
.BeginGroup = True
.Caption = sMenu
.FaceId = 197
.Style = msoButtonIconAndCaption
.OnAction = "myMacro"
End With

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"bmm" <newsgroup.com wrote in message
...
How can I add a new tool bar to the Excel using VBA.
One way tried is read the resource file and copy on clipboard and paste it
on command bar.
But when we open more than one instances of XL, tool bar is not visible in
all of them completely.

How can we handle this?

Thanks and rgds