Hi All

I have created an addin that will be distributed to users but have a problem
with how it is working. My OS is Win XP with Excel 2002. Broadly speaking I
want to achieve the following:

1. Junior members of staff dump data from our database into Excel and run
one of the macros in this addin to produce charts and tables from the raw
data. In addition this macro creates an 'update' button for use later.
2. This workbook is then saved and the process repeated for different criteria
3. Senior members of staff at a later date remove 'bad' data from the
charts' data source.
4. The senior staff then click the 'update' button on the chart. This runs
another macro in the addin file that extracts the trendline slope data from
one of the charts to update a table of regression data so that it reflects
the changes in the raw data.
5. The charts and tables are then extracted by secrataries to put in
publications

The problem I am having is that when the button is created and the macro is
assigned to it (in step 1), the full path is assigned. This means, for
example, that if somebody else were to click the 'update' button in a
workbook created by me, they would get an error message. The message states
that the addin file was already opened and that two workbooks with the same
name cannot be open etc etc. On closer inspection the macro assigned to the
button contains the full path to the addin file but for its location on my
machine ie c:\...\Andy\...\Addins. Is there any way of assigning the macro
name to the button so that it will refer to the local file rather than the
one on the machine that originally created the button?

Hope this makes sense!

Thanks

Andy

It would be a much better design to have the add-in create (when opened)
menubars/menus/toolbars to be used to execute your add-in's macros rather
than to use buttons on other workbooks.

Lacking that you may need a macro in your workbook(s) that uses the
ChangeLinks method to connect the buttons in the workbook to the add-in.

--
Jim
"Andy" wrote in message
...
| Hi All
|
| I have created an addin that will be distributed to users but have a
problem
| with how it is working. My OS is Win XP with Excel 2002. Broadly
speaking I
| want to achieve the following:
|
| 1. Junior members of staff dump data from our database into Excel and run
| one of the macros in this addin to produce charts and tables from the raw
| data. In addition this macro creates an 'update' button for use later.
| 2. This workbook is then saved and the process repeated for different
criteria
| 3. Senior members of staff at a later date remove 'bad' data from the
| charts' data source.
| 4. The senior staff then click the 'update' button on the chart. This runs
| another macro in the addin file that extracts the trendline slope data
from
| one of the charts to update a table of regression data so that it reflects
| the changes in the raw data.
| 5. The charts and tables are then extracted by secrataries to put in
| publications
|
| The problem I am having is that when the button is created and the macro
is
| assigned to it (in step 1), the full path is assigned. This means, for
| example, that if somebody else were to click the 'update' button in a
| workbook created by me, they would get an error message. The message
states
| that the addin file was already opened and that two workbooks with the
same
| name cannot be open etc etc. On closer inspection the macro assigned to
the
| button contains the full path to the addin file but for its location on
my
| machine ie c:\...\Andy\...\Addins. Is there any way of assigning the
macro
| name to the button so that it will refer to the local file rather than the
| one on the machine that originally created the button?
|
| Hope this makes sense!
|
| Thanks
|
| Andy

Thanks Jim, I don't know why I didn't think of that seeing as the add-in
already creates menus - I'll put it down to lack of experience!

Andy

"Jim Rech" wrote:

It would be a much better design to have the add-in create (when opened)
menubars/menus/toolbars to be used to execute your add-in's macros rather
than to use buttons on other workbooks.

Lacking that you may need a macro in your workbook(s) that uses the
ChangeLinks method to connect the buttons in the workbook to the add-in.

--
Jim
"Andy" wrote in message
...
| Hi All
|
| I have created an addin that will be distributed to users but have a
problem
| with how it is working. My OS is Win XP with Excel 2002. Broadly
speaking I
| want to achieve the following:
|
| 1. Junior members of staff dump data from our database into Excel and run
| one of the macros in this addin to produce charts and tables from the raw
| data. In addition this macro creates an 'update' button for use later.
| 2. This workbook is then saved and the process repeated for different
criteria
| 3. Senior members of staff at a later date remove 'bad' data from the
| charts' data source.
| 4. The senior staff then click the 'update' button on the chart. This runs
| another macro in the addin file that extracts the trendline slope data
from
| one of the charts to update a table of regression data so that it reflects
| the changes in the raw data.
| 5. The charts and tables are then extracted by secrataries to put in
| publications
|
| The problem I am having is that when the button is created and the macro
is
| assigned to it (in step 1), the full path is assigned. This means, for
| example, that if somebody else were to click the 'update' button in a
| workbook created by me, they would get an error message. The message
states
| that the addin file was already opened and that two workbooks with the
same
| name cannot be open etc etc. On closer inspection the macro assigned to
the
| button contains the full path to the addin file but for its location on
my
| machine ie c:\...\Andy\...\Addins. Is there any way of assigning the
macro
| name to the button so that it will refer to the local file rather than the
| one on the machine that originally created the button?
|
| Hope this makes sense!
|
| Thanks
|
| Andy