Using Excel 2003 I have written a set of macros that is used with workbooks
with a lot of hand entered data. Right now the macros need frequent
updates as more features are added. To facilitate providing updates, I
felt it was necessary to separate the code from the data so that the user
didn't have to do anything when the code was upgraded.

To accomplish this, I created an addin containing the macros. Now I just
email the new addin and users are good to go. The problem is that the
addin is automatically attached to every workbook I open and there are a
number of reasons that is not good.

Is there any way I can have the addin attached only to selected workbooks?
- OR-
Is there a way other than using addins to effectively separate the code
from the data so that the code can be updated independently of the data?

Thanks for any help.

The only reasons I can think of to have a workbook linked to an XLA
addin is when you either
-reference a UDF from the XLA in the workbook
- attach an on-sheet control to the workbook that references the XLA
- create a formula or defined name in the workbook that contains an
external reference to a cell on a worksheet in the XLA

None of these things should be happening when you open a new workbook
unless you want them to.

BTW you might want to look at my automatically reversioning addin
loader to solve potentail problems with shared updated addins.
http://www.Decisionmodels.com/downloads.htm

Charles Williams
Excel MVP
The Excel Calculation Site
http://www.DecisionModels.com


>Using Excel 2003 I have written a set of macros that is used with workbooks
>with a lot of hand entered data. Right now the macros need frequent
>updates as more features are added. To facilitate providing updates, I
>felt it was necessary to separate the code from the data so that the user
>didn't have to do anything when the code was upgraded.
>
>To accomplish this, I created an addin containing the macros. Now I just
>email the new addin and users are good to go. The problem is that the
>addin is automatically attached to every workbook I open and there are a
>number of reasons that is not good.
>
>Is there any way I can have the addin attached only to selected workbooks?
>- OR-
>Is there a way other than using addins to effectively separate the code
>from the data so that the code can be updated independently of the data?
>
>Thanks for any help.

Thanks for replying. I'm sorry, but I don't quite undestand your reply.
When, in Excel, I go to Tools/Addins and check an available addin, it is
linked to every workbook I open from then on . That is exactly the
behaviour I don't want. I want it to be attached only to selected
workbooks, but I don't know how to accomplish that.

On Wed, 26 May 2010 16:05:02 +0100, Charles Williams
<(E-Mail Removed)> wrote:

>The only reasons I can think of to have a workbook linked to an XLA
>addin is when you either
>-reference a UDF from the XLA in the workbook
>- attach an on-sheet control to the workbook that references the XLA
>- create a formula or defined name in the workbook that contains an
>external reference to a cell on a worksheet in the XLA
>
>None of these things should be happening when you open a new workbook
>unless you want them to.
>
>BTW you might want to look at my automatically reversioning addin
>loader to solve potentail problems with shared updated addins.
>http://www.Decisionmodels.com/downloads.htm
>
>Charles Williams
>Excel MVP
>The Excel Calculation Site
>http://www.DecisionModels.com
>
>
>>Using Excel 2003 I have written a set of macros that is used with workbooks
>>with a lot of hand entered data. Right now the macros need frequent
>>updates as more features are added. To facilitate providing updates, I
>>felt it was necessary to separate the code from the data so that the user
>>didn't have to do anything when the code was upgraded.
>>
>>To accomplish this, I created an addin containing the macros. Now I just
>>email the new addin and users are good to go. The problem is that the
>>addin is automatically attached to every workbook I open and there are a
>>number of reasons that is not good.
>>
>>Is there any way I can have the addin attached only to selected workbooks?
>>- OR-
>>Is there a way other than using addins to effectively separate the code
>>from the data so that the code can be updated independently of the data?
>>
>>Thanks for any help.

Describe what you mean by "linked"

Regards,
Peter T

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for replying. I'm sorry, but I don't quite undestand your reply.
> When, in Excel, I go to Tools/Addins and check an available addin, it is
> linked to every workbook I open from then on . That is exactly the
> behaviour I don't want. I want it to be attached only to selected
> workbooks, but I don't know how to accomplish that.
>
> On Wed, 26 May 2010 16:05:02 +0100, Charles Williams
> <(E-Mail Removed)> wrote:
>
>>The only reasons I can think of to have a workbook linked to an XLA
>>addin is when you either
>>-reference a UDF from the XLA in the workbook
>>- attach an on-sheet control to the workbook that references the XLA
>>- create a formula or defined name in the workbook that contains an
>>external reference to a cell on a worksheet in the XLA
>>
>>None of these things should be happening when you open a new workbook
>>unless you want them to.
>>
>>BTW you might want to look at my automatically reversioning addin
>>loader to solve potentail problems with shared updated addins.
>>http://www.Decisionmodels.com/downloads.htm
>>
>>Charles Williams
>>Excel MVP
>>The Excel Calculation Site
>>http://www.DecisionModels.com
>>
>>
>>>Using Excel 2003 I have written a set of macros that is used with
>>>workbooks
>>>with a lot of hand entered data. Right now the macros need frequent
>>>updates as more features are added. To facilitate providing updates, I
>>>felt it was necessary to separate the code from the data so that the user
>>>didn't have to do anything when the code was upgraded.
>>>
>>>To accomplish this, I created an addin containing the macros. Now I just
>>>email the new addin and users are good to go. The problem is that the
>>>addin is automatically attached to every workbook I open and there are a
>>>number of reasons that is not good.
>>>
>>>Is there any way I can have the addin attached only to selected
>>>workbooks?
>>>- OR-
>>>Is there a way other than using addins to effectively separate the code
>>>from the data so that the code can be updated independently of the data?
>>>
>>>Thanks for any help.
>

The add-in is not "linked" or "attached" to the open workbooks.

It is just available for use with any open workbook.

That's the nature of installed/loaded add-ins.

To have it available for certain workbooks only, load the add-in using code
in those workbooks.

Private Sub Workbook_Open()
AddIns("Myaddin").Installed = True
End Sub

Unload when closing those workbooks.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
AddIns("Myaddin").Installed = False
End Sub

Both these events are stored in Thisworkbook module of any workbook you wish
to have access to Myadd-in macros.


Gord Dibben MS Excel MVP


On Wed, 26 May 2010 13:35:57 -0400, (E-Mail Removed) wrote:

>Thanks for replying. I'm sorry, but I don't quite undestand your reply.
>When, in Excel, I go to Tools/Addins and check an available addin, it is
>linked to every workbook I open from then on . That is exactly the
>behaviour I don't want. I want it to be attached only to selected
>workbooks, but I don't know how to accomplish that.
>
>On Wed, 26 May 2010 16:05:02 +0100, Charles Williams
><(E-Mail Removed)> wrote:
>
>>The only reasons I can think of to have a workbook linked to an XLA
>>addin is when you either
>>-reference a UDF from the XLA in the workbook
>>- attach an on-sheet control to the workbook that references the XLA
>>- create a formula or defined name in the workbook that contains an
>>external reference to a cell on a worksheet in the XLA
>>
>>None of these things should be happening when you open a new workbook
>>unless you want them to.
>>
>>BTW you might want to look at my automatically reversioning addin
>>loader to solve potentail problems with shared updated addins.
>>http://www.Decisionmodels.com/downloads.htm
>>
>>Charles Williams
>>Excel MVP
>>The Excel Calculation Site
>>http://www.DecisionModels.com
>>
>>
>>>Using Excel 2003 I have written a set of macros that is used with workbooks
>>>with a lot of hand entered data. Right now the macros need frequent
>>>updates as more features are added. To facilitate providing updates, I
>>>felt it was necessary to separate the code from the data so that the user
>>>didn't have to do anything when the code was upgraded.
>>>
>>>To accomplish this, I created an addin containing the macros. Now I just
>>>email the new addin and users are good to go. The problem is that the
>>>addin is automatically attached to every workbook I open and there are a
>>>number of reasons that is not good.
>>>
>>>Is there any way I can have the addin attached only to selected workbooks?
>>>- OR-
>>>Is there a way other than using addins to effectively separate the code
>>>from the data so that the code can be updated independently of the data?
>>>
>>>Thanks for any help.

After serious thinking (E-Mail Removed) wrote :
> Thanks for replying. I'm sorry, but I don't quite undestand your reply.
> When, in Excel, I go to Tools/Addins and check an available addin, it is
> linked to every workbook I open from then on . That is exactly the
> behaviour I don't want. I want it to be attached only to selected
> workbooks, but I don't know how to accomplish that.
>
> On Wed, 26 May 2010 16:05:02 +0100, Charles Williams
> <(E-Mail Removed)> wrote:
>
>> The only reasons I can think of to have a workbook linked to an XLA
>> addin is when you either
>> -reference a UDF from the XLA in the workbook
>> - attach an on-sheet control to the workbook that references the XLA
>> - create a formula or defined name in the workbook that contains an
>> external reference to a cell on a worksheet in the XLA
>>
>> None of these things should be happening when you open a new workbook
>> unless you want them to.
>>
>> BTW you might want to look at my automatically reversioning addin
>> loader to solve potentail problems with shared updated addins.
>> http://www.Decisionmodels.com/downloads.htm
>>
>> Charles Williams
>> Excel MVP
>> The Excel Calculation Site
>> http://www.DecisionModels.com
>>
>>
>>> Using Excel 2003 I have written a set of macros that is used with workbooks
>>> with a lot of hand entered data. Right now the macros need frequent
>>> updates as more features are added. To facilitate providing updates, I
>>> felt it was necessary to separate the code from the data so that the user
>>> didn't have to do anything when the code was upgraded.
>>>
>>> To accomplish this, I created an addin containing the macros. Now I just
>>> email the new addin and users are good to go. The problem is that the
>>> addin is automatically attached to every workbook I open and there are a
>>> number of reasons that is not good.
>>>
>>> Is there any way I can have the addin attached only to selected workbooks?
>>> - OR-
>>> Is there a way other than using addins to effectively separate the code
>>> from the data so that the code can be updated independently of the data?
>>>
>>> Thanks for any help.

If I understand you correctly, what you're saying is that you don't
want the procedures in your addin to be used on just any open workbook,
but rather ONLY those workbooks that the addin was designed to be used
with. If this is the case then I make the following recommendations:

1. Use a workbook-level defined name as an 'flag' (identifier) that
the workbook belongs to your addin.

Alternatively, you could use a Custom Property stored in the workbook
file to accomplish the same thing.

2. Add a class module to your addin that monitors events. Place code
in here to disable any menus/toolbars if the active workbook does not
have the flag that identifies it as belonging to your addin.

Alternatively, you could make the menus/toolbar visible (or not)
depending on the 'flag' status of the currently active workbook.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc