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