I need to send updatees to end users. Update formulas and formates to a
worksheet that they use. I normally send out a new workbook but that causes
them to have to reenter their inflormation. I need a way to send correction
and updates so that they do not have to re-enter any information.

Look into creating an add in, this would allow you to update your code from a
centralized location rather than relying everyone to use new sheets/re-enter
data.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"David A." wrote:

I need to send updatees to end users. Update formulas and formates to a
worksheet that they use. I normally send out a new workbook but that causes
them to have to reenter their inflormation. I need a way to send correction
and updates so that they do not have to re-enter any information.

Look into creating an add in,

I was going to suggest that exact same thing, but upon rereading the
original post, he wants to update "formulas and formates [sic]". Those don't
readily lend themselves to an Add-In approach.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"John Bundy" (remove) wrote in message
...
Look into creating an add in, this would allow you to update your code
from a
centralized location rather than relying everyone to use new
sheets/re-enter
data.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"David A." wrote:

I need to send updatees to end users. Update formulas and formates to a
worksheet that they use. I normally send out a new workbook but that
causes
them to have to reenter their inflormation. I need a way to send
correction
and updates so that they do not have to re-enter any information.

Hi David,

I feel your pain. This has been an issue for me as well. I have found one
suggestion, though.

On page 509 of his book "VBA and Macros for Microsoft Excel," Bill Jelen
talks about a solution to this problem. He suggests using two workbooks, one
for the code and one to store information and call the code on the other
workbook. The code workbook contains all the code necessary to process the
data. The data workbook has as few lines as possible, and each line is
tested as much as possible.

The data workbook opens the workbook as needed and hides it in the
background. After processing or upon closing, the data workbook closes the
code workbook. This method has the advantage of keeping the data workbook
sizes optimized and allowing you, the developer, to send out as many updates
as required without resetting data. If the data does have to be modified as
a result of the update, the code workbook could run a routine called
UpdateData or something.

Hopefully this method can work for you. Read more about it in Bill's book.

HTH,
Pflugs

"David A." wrote:

I need to send updatees to end users. Update formulas and formates to a
worksheet that they use. I normally send out a new workbook but that causes
them to have to reenter their inflormation. I need a way to send correction
and updates so that they do not have to re-enter any information.

On Jul 31, 9:10 am, Pflugs wrote:
Hi David,

I feel your pain. This has been an issue for me as well. I have found one
suggestion, though.

On page 509 of his book "VBA and Macros for Microsoft Excel," Bill Jelen
talks about a solution to this problem. He suggests using two workbooks, one
for the code and one to store information and call the code on the other
workbook. The code workbook contains all the code necessary to process the
data. The data workbook has as few lines as possible, and each line is
tested as much as possible.

The data workbook opens the workbook as needed and hides it in the
background. After processing or upon closing, the data workbook closes the
code workbook. This method has the advantage of keeping the data workbook
sizes optimized and allowing you, the developer, to send out as many updates
as required without resetting data. If the data does have to be modified as
a result of the update, the code workbook could run a routine called
UpdateData or something.

Hopefully this method can work for you. Read more about it in Bill's book.

HTH,
Pflugs



"David A." wrote:
I need to send updatees to end users. Update formulas and formates to a
worksheet that they use. I normally send out a new workbook but that causes
them to have to reenter their inflormation. I need a way to send correction
and updates so that they do not have to re-enter any information.- Hide quoted text -

- Show quoted text -

So Pflugs I think that your concept sound good. Most of the code in a
hidden workbook and the data in a data workbook.
Kind of like a backend database with data and a front end with
userforms, code, queries and reports. Updates are mainly just
replacing the frontend.
For this to work I would need a code wb that I could securely hide the
code and most worksheets such as lookup tables and lists. Could you
suggest the best way to hide the wb and or the code and the lookup
tables?

What would you judge to be the issues with this type of separation?

I would have to modify code to look for a certain workbook and in my
case the user template file has a different name for each user. This
file name can change with each version upgrade of that file. So then
my code have to consider and deal with the unique filename rather than
pointing to MyWorkbook or ActiveWorkbook. How would you suggest
approaching this problem?

Could I ask my client to put this updated code wb in the same folder
as their template worksheet and somehow have the code look for a
certain worksheet in whatever xls file is in that folder? In my user
template I already have a named cell that captures the filename of
itself that I use in code now.

I like the concept as it is far more managable I think. I would like
to see some more feedback from others who have done this with Excel.

Dennis