How to automatically add Toolbar buttons and run add-in?

[Khaki]

Hi,
What I would like to do this, I would like to distribute my custo
add-in. And after installation, I would like to addl a custom toolba
button in Excel, and attach a function to this button from th
installed add-in.
I would like it to apply to all excel files. Does this mean I have t
add the add-in to the XLSTART directory? How would I add button to th
toolbar automatically?

Basically I would like to do something similar to how Acrobat get
installed into Excel with a button and the button is (I assume) i
attached to PDFMaker.xla. I still can't figure out how to add th
buttons automatically,

Thanks in advance

 

[Bob Phillips]

Hi Khaki,

In summary, you should create a temporary toolbar in the workbook_Open event
of your add-in. Then when Excel starts, as long as tyhe add-in is installed,
the toolbar will be available. If you also remove it the
workbook_BeforeClose event it will be removed immeditaely the add-in is
de-installed, but will not be ther next time Excel start5s as it is
temporary.

The add-in does not need to be in XLStart as it is installed, so Excel will
know where it is.

Here is some sample code to create a toolbar as suggested. This code would
go in the ThisWorkbok code module.

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 appMenu As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)

appMenu = "My Toolbar"

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

End Sub

Private Sub Workbook_Open()
Dim oCB As CommandBar

appMenu = "My Toolbar"

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

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

With oCB
With .Controls.Add(Type:=msoControlButton)
.Caption = appMenu & " Toolbar"
.Style = msoButtonCaption
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Open File"
.FaceId = 23
.Style = msoButtonIconAndCaption
.OnAction = "OpenFiles"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Sort Results"
.FaceId = 210
.Style = msoButtonIconAndCaption
.OnAction = "BCCCSort"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Player"
.FaceId = 316
.Style = msoButtonIconAndCaption
.OnAction = "NewEntry"
End With
With .Controls.Add(Type:=msoControlDropdown)
.BeginGroup = True
.Caption = "Delete"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Delete "
.Style = msoButtonCaption
.OnAction = "RemoveEntry "
.Parameter = "Toolbar"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Sheet"
.FaceId = 18
.Style = msoButtonIconAndCaption
.OnAction = "NewSheet"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "New Workbook"
.FaceId = 245
.Style = msoButtonIconAndCaption
.OnAction = "NewBook"
End With
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "About..."
.FaceId = 941
.Style = msoButtonIconAndCaption
.OnAction = "About"
End With
.Visible = True
.Position = msoBarTop
End With


End Sub


--

HTH

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

 

[Jan Karel Pieterse]

Hi Khaki,

What I would like to do this, I would like to distribute my custon
add-in. And after installation, I would like to addl a custom toolbar
button in Excel, and attach a function to this button from the
installed add-in.
I would like it to apply to all excel files. Does this mean I have to
add the add-in to the XLSTART directory? How would I add button to the
toolbar automatically?

Here is my boilerplate text on distributing macros.

Many users tend to put their macros in their personal.xls file. Nothing
wrong with that, because that is what it is there for. But what if you
like your macros so much you decide others might benefit?

You might be tempted to copy your personal.xls onto a floppy and give
that to others. Don't!
Also don't copy your xlb file to others to give them your toolbars, you
will overwrite their customisations..

I would be very distressed if you would come and hijack my personal.xls
and excel.xlb files!

What you should do is create a new workbook with all your code *and
toolbars* and distribute *that* file (maybe saved as add-in).

About toolbars:

You can attach a toolbar to a workbook. When this workbook is loaded, XL
checks if the toolbar is on the system. If not, it copies the toolbar
from the workbook to the system.

After creating *or changing* the toolbar, you should attach the toolbar
to your workbook:

- activate the workbook to which you want to attach the toolbar
- Rightclick the toolbar, select 'customize'
- Click 'Attach' (Toolbars Tab)
- If the workbook already contains a toolbar by that name, delete it
first by clicking on it on the righthand side and choosing Delete.
- Select your toolbar (on the left) and press 'copy'
- Save the workbook (optionally: save_as an add-in).

Also, You should include code that deletes the toolbar when your workbook
or add-in is closed, so that when you deliver a new version of your
workbook the new toolbar will be used i.s.o the old one. You can do that
in the Thisworkbook module, using the Workbook_beforeClose event:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
On Error Resume Next 'In case Toolbar is absent
Application.CommandBars("YourBarsName").Delete
End Sub

For an example how to automate installation of an addin, download one of
my utilities (like the Name Manager) from my site below.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Khaki]

Hi Jan
Thanks for the great info.
Very much appreciated!

K