How do I make a button appear when the custom Add-in is selected?

[Anya]

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.

 

[john]

have a look at this site:

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

 

[Anya]

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

 

[Chip Pearson]

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)