How to create a workboook specific Excel Add in

Question

I would like to create a excel Add in which creates some additional toolbars and Menu buttons. But I want this addin to load only when a specific workbook is opened. I dont want to load the Addin if anyother workbook is open.

I would like to know what are the possible ways to solve this problem and what is the best approach to implement this Add in (XLA or VSTO or COM Addin).

I dont want user to know my Addin path, VbA code required to load/Initialize the addin.

Answer 1

This sounds like a good case for a VSTO Document project; unlike add-ins, which extend the whole application, and as such apply to any open document, a VSTO document project is a customization of a specific document, to which extra code is attached.

Answer 2

Create the toolbar at the beginning, but set the toolbar visibility to false. Capture the workbook being opened with an event handler for the AddIn.Application.WorkbookOpen event, and determine if the workbook is the specific one you want. At that point you can set the visibility of the toolbar.

You can also trap the AddIn.Application.WorkbookActivate event and hide the toolbar back again if the active workbook is not the specific workbook.

Remember to have a member variable declared at the ThisAddIn level to keep a reference to the toolbar!

Answer 3

If you really only want the addin to load when a certain workbook opens why not load it in the Open event of the relevant workbook

' code in the ThisWorkbook module

Private Sub Workbook_Open()

Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks("myAddin.xla")
If wb Is Nothing Then
    Set wb = Application.Workbooks.Open("c:\path\myAddin.xls")
End If

End Sub

Alternatively, any other Workbook or Addin can inlcude code to trap Excel Application Level events such as Workbook.Open, and if the name of the newly opened work is the one you are interested in do whatever, ie load the Addin