Hi, I've created an Add-in by writing VBA code in an Excel XP workbook and
then saving it as and .xla file. This is my first time creating an Add-in and
I see my Add-in in the Tools -> Add-Ins menu so that's exciting
Now I'd like to add a button to the Tool bar and have that button appear
whenever myAddIn is enabled. And then I'd like my code (which is in my
Add-in) to run when the user clicks this new button. Could someone help
please? I see few similar questions here but not one that would tell me
exaclty how to do that.

Thank you.

have a look at this site:

http://www.jkp-ads.com/articles/DistributeMacro00.asp
--
jb


"Anya" wrote:

> Hi, I've created an Add-in by writing VBA code in an Excel XP workbook and
> then saving it as and .xla file. This is my first time creating an Add-in and
> I see my Add-in in the Tools -> Add-Ins menu so that's exciting
> Now I'd like to add a button to the Tool bar and have that button appear
> whenever myAddIn is enabled. And then I'd like my code (which is in my
> Add-in) to run when the user clicks this new button. Could someone help
> please? I see few similar questions here but not one that would tell me
> exaclty how to do that.
>
> Thank you.
>

Thank you. I also just saw an answer by Ron de Bruin which seems to have
exactly what I need at this page http://www.contextures.com/xlToolbar02.html


"john" wrote:

> have a look at this site:
>
> http://www.jkp-ads.com/articles/DistributeMacro00.asp
> --
> jb
>
>
> "Anya" wrote:
>
> > Hi, I've created an Add-in by writing VBA code in an Excel XP workbook and
> > then saving it as and .xla file. This is my first time creating an Add-in and
> > I see my Add-in in the Tools -> Add-Ins menu so that's exciting
> > Now I'd like to add a button to the Tool bar and have that button appear
> > whenever myAddIn is enabled. And then I'd like my code (which is in my
> > Add-in) to run when the user clicks this new button. Could someone help
> > please? I see few similar questions here but not one that would tell me
> > exaclty how to do that.
> >
> > Thank you.
> >

Put the following code in the ThisWorkbook module of your XLA project.
It creates a button on the Standard command bar with a caption of
"Click Me".

Option Explicit

Private WithEvents MyButton As Office.CommandBarButton
Private Const C_TAG = "MyAddInName"

Private Sub MyButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
MsgBox "Hello World"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim C As Office.CommandBarControl
On Error Resume Next
For Each C In Application.CommandBars.FindControls(Tag:=C_TAG)
C.Delete
Next C
End Sub

Private Sub Workbook_Open()
Dim C As Office.CommandBarControl
' clean up first
On Error Resume Next
For Each C In Application.CommandBars.FindControls(Tag:=C_TAG)
C.Delete
Next C
On Error GoTo 0
Set MyButton =
Application.CommandBars(3).Controls.Add(Type:=msoControlButton,
temporary:=True)
' create a new item on the Standard command bar, id = 9.
With MyButton
.Style = msoButtonIconAndCaption
.Caption = "Click Me"
.Tag = C_TAG
End With
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 13 May 2009 12:06:19 -0700, Anya
<(E-Mail Removed)> wrote:

>Hi, I've created an Add-in by writing VBA code in an Excel XP workbook and
>then saving it as and .xla file. This is my first time creating an Add-in and
>I see my Add-in in the Tools -> Add-Ins menu so that's exciting
>Now I'd like to add a button to the Tool bar and have that button appear
>whenever myAddIn is enabled. And then I'd like my code (which is in my
>Add-in) to run when the user clicks this new button. Could someone help
>please? I see few similar questions here but not one that would tell me
>exaclty how to do that.
>
>Thank you.