I have been trying to slug out the solution to this for a couple of days now;
I know just enough to look up solutions in the VBA help and try and figure it
out from there.

Anyway, what I'm trying to do for my office is automate some document
controls that we now require in the form of custom headers and footers; some
of the data is created/stored in custom document properties and other data
are custom formulae--I have no problem with this. The first time I click
save or print when the document is open, I want it to validate the custom
document properties and/or give me a chance to change them ... and then
update the headers & footers for each worksheet in the workbook.

I have setup a new default Book.xls & Sheet.xls for every new document I
create. So, in new workbooks, everything I want to happen works superbly;
it's the old workbooks that I can't figure out.

Ideally, when I open an old workbook, I'd like to have a button on a custom
toolbar that would run the code from my default Book.xls on the old document,
storing my desired custom headers & footers. I'd also like to add the
functionality of the BeforeSave & BeforePrint from that works so brilliantly
in the default Book.xls. I'm thinking the only way to do this is to copy the
code verbatim from the default Book.xls into my old document with this
button? This could potentially present a problem when I have to distribute
it to the 30 or so people on my team across the country if there is any hard
file paths involved. Any ideas? Remember, I know just enough to be
dangerous.

Thanks, in advance, for your help.--JT

Use envirnoment parameters to get the users My document directory

UserName = environ("UserName") or replace user name with any of the
environmental parameters found below.


to get a list of all the environmental parameter on your PC you need to open
a Dos window

From XP
1) Start - Run
2) type in run box CMD.EXE
3) A dos window wil open. On the command line type
SET then return

From vista
1) Start - Find
2) In the find box type CMD.Exe
3) A dos window wil open. On the command line type
SET then return


"JT Klipfer" wrote:

I have been trying to slug out the solution to this for a couple of days now;
I know just enough to look up solutions in the VBA help and try and figure it
out from there.

Anyway, what I'm trying to do for my office is automate some document
controls that we now require in the form of custom headers and footers; some
of the data is created/stored in custom document properties and other data
are custom formulae--I have no problem with this. The first time I click
save or print when the document is open, I want it to validate the custom
document properties and/or give me a chance to change them ... and then
update the headers & footers for each worksheet in the workbook.

I have setup a new default Book.xls & Sheet.xls for every new document I
create. So, in new workbooks, everything I want to happen works superbly;
it's the old workbooks that I can't figure out.

Ideally, when I open an old workbook, I'd like to have a button on a custom
toolbar that would run the code from my default Book.xls on the old document,
storing my desired custom headers & footers. I'd also like to add the
functionality of the BeforeSave & BeforePrint from that works so brilliantly
in the default Book.xls. I'm thinking the only way to do this is to copy the
code verbatim from the default Book.xls into my old document with this
button? This could potentially present a problem when I have to distribute
it to the 30 or so people on my team across the country if there is any hard
file paths involved. Any ideas? Remember, I know just enough to be
dangerous.

Thanks, in advance, for your help.--JT