Attaching a custom toolbar to a .xla addin for distribution

[garybug]

I have written an Addin, and have created a toolbar that fires the DisplayForm function, but my problem is when I send it to a colleague, the toolbar is not available. I have tried the 'Attach' functionality, but it will only allow me to attach to a Workbook, not the addin .xla itself. I haven't explicitly created the toolbar in VBA, but thought that the toolbar would have attached itself to the addin, perhaps via the ThisWorkbook object in Microsoft Excel Objects (in the VBA Explorer window).

Any advice on how I can attach it?

Cheers Much.

 

[combo]

Toolbars in workbook are not easy to handle, when you add a toolbar to workbook and open it, it starts its own life, sticks to excelXX.xlb file. It also points to macro location valid when created. When a workbook is saved as an add-in, it usually is stored in another location, moreover, it can't be changed. You do not also have programmatic access to collection of toolbars within the workbook.
So you can either use Workbook_Open event to manage toolbars (and assign proper macro file), or, what is frequently used, dynamically create and destroy the toolbar using Woorkbook_Open/Close (or Workbook_AddinInstall, more risky) event handlers.

combo

 

[shetlandbob]

You can create a menu command that you could assign the display form function to?

On the AutoOpen function add a menu item and remove it on AutoClose (for your add-in).

 

[garybug]

Thanks all. I had a feeling I might need to re-create it each time. I think my other option would be to add it to a hidden workbook, but it's a bit of a cheap way of doing it! I'll go ahead & recreate /destroy it each time.

Cheers..