Private Sub Workbook_Open()

Dim oCtl As CommandBarControl
With Application.CommandBars("standard")
On Error Resume Next
.Controls("Format me").Delete
On Error goto 0
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Format me"
.OnAction = "Mymacro"
.FaceId = 527
End With
End With

End Sub

--
Regards,
Tom Ogilvy


"cg" wrote in message
...
Hi all,

I created an add-in with the following code in it's open
event. My problem is this button is created everytime a
workbook is opened. yikes! Should I look for the button
before adding it and if so how? Is this the best way to
add a button for my macro in the add-in? Any help would
be greatly appreciated.

Private Sub Workbook_Open()

Dim oCtl As CommandBarControl
With Application.CommandBars("standard")
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Format me"
.OnAction = "Mymacro"
.FaceId = 527
End With
End With

End Sub

Thanks again Tom, I can always count on you to save my
day!
-----Original Message-----
Private Sub Workbook_Open()

Dim oCtl As CommandBarControl
With Application.CommandBars("standard")
On Error Resume Next
.Controls("Format me").Delete
On Error goto 0
Set oCtl = .Controls.Add(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Format me"
.OnAction = "Mymacro"
.FaceId = 527
End With
End With

End Sub

--
Regards,
Tom Ogilvy


"cg" wrote in message
...
Hi all,

I created an add-in with the following code in it's
open
event. My problem is this button is created everytime a
workbook is opened. yikes! Should I look for the button
before adding it and if so how? Is this the best way to
add a button for my macro in the add-in? Any help would
be greatly appreciated.

Private Sub Workbook_Open()

Dim oCtl As CommandBarControl
With Application.CommandBars("standard")
Set oCtl = .Controls.Add
(Type:=msoControlButton)
With oCtl
.BeginGroup = True
.Caption = "Format me"
.OnAction = "Mymacro"
.FaceId = 527
End With
End With

End Sub



.