Does anyone know how to create a toolbar that will run
some code in an .xla? I want this toolbar to be
displayed when the add-in is loaded (checked) and go away
when the add-in is unloaded (unchecked). Thanks.

Hi Phill,

Does anyone know how to create a toolbar that will run
some code in an .xla? I want this toolbar to be
displayed when the add-in is loaded (checked) and go away
when the add-in is unloaded (unchecked).


Excel keeps toolbar and menubar customizations in a file with the
extension .xlb. The exact filename depends on Excel version and
install, but usually is: Excel9.xlb or Excel.xlb or Username8.xlb.
Often this file can be found in your WINDOWS directory.

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

After that you save the workbook as filetype Add-in and you're done.

Regards,

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

Thanks. My toolbar is available but is not visible until
I selected it. It deletes just fine. Is there a way to
get the toolbar to be visible by default? Thanks again.

-----Original Message-----
Hi Phill,

Does anyone know how to create a toolbar that will run
some code in an .xla? I want this toolbar to be
displayed when the add-in is loaded (checked) and go
away
when the add-in is unloaded (unchecked).


Excel keeps toolbar and menubar customizations in a file
with the
extension .xlb. The exact filename depends on Excel
version and
install, but usually is: Excel9.xlb or Excel.xlb or
Username8.xlb.
Often this file can be found in your WINDOWS directory.

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

After that you save the workbook as filetype Add-in and
you're done.

Regards,

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

.