Automatic updating a footer for an entire workbook

Maj Bay · May 18th, 2002, 01:23 PM

Here is my challenge:

I have about 150 employees that use an Excel workbook as a timesheet. Each workbook has 26 pages.

I need to revise the footer for every page of every workbook. I would like to automate this as much as possible (most of the employees are still looking for the "any" key if you know what I mean!)

I have a small seven line chunk of code that will update the footer, but I need to get it into the workbooks as a "Workbook Before Print" procedure.

Ideally I would like to email a worksheet as an attachment. The worksheet would have one big button on it. When that button is pressed it would:
1. Open the "File-Open" dialog (so the employee could find their Excel timesheet)
2. Stuff the seven line chunk of code that I have into the Excel timesheet as a "workbook before print" procedure
3. Save the Excel Timesheet
4. Pop open an "Update Complete" window so the employee knows it was accomplished.

Has anyone had to do anything similar to this before? I would appreciate any help!

Thanks,

Maj Bay

Chris Davison · May 18th, 2002, 01:43 PM

Where's the "any" key ?

(_8 ()

stevebausch · May 18th, 2002, 01:49 PM

If you had a network folder accessible to all, you could send a shortcut to the file to everyone, and just let them load the new version to their station.

Before I continue to say things that may not be accurate/appreciated, keep in mind that I tend to take advantage of threads that have zero response

26 pages correspond to a year's worth of payroll, correct? And that's why it might be hard to swap out the workbook in the middle of the year?

I know that what you ask (self-modifying code) can be done, just never done it.

http://www.cgi101.com/~clr/vba/routines/selfmodify.html

Take a look at this page, see if it helps you.

Or perhaps this page:

http://support.microsoft.com/default...;EN-US;q219905

[ This Message was edited by: stevebausch on 2002-05-18 13:15 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 18th, 2002, 01:23 PM	#1
Maj Bay New Member Join Date: May 2002 Posts: 1	Here is my challenge: I have about 150 employees that use an Excel workbook as a timesheet. Each workbook has 26 pages. I need to revise the footer for every page of every workbook. I would like to automate this as much as possible (most of the employees are still looking for the "any" key if you know what I mean!) I have a small seven line chunk of code that will update the footer, but I need to get it into the workbooks as a "Workbook Before Print" procedure. Ideally I would like to email a worksheet as an attachment. The worksheet would have one big button on it. When that button is pressed it would: 1. Open the "File-Open" dialog (so the employee could find their Excel timesheet) 2. Stuff the seven line chunk of code that I have into the Excel timesheet as a "workbook before print" procedure 3. Save the Excel Timesheet 4. Pop open an "Update Complete" window so the employee knows it was accomplished. Has anyone had to do anything similar to this before? I would appreciate any help! Thanks, Maj Bay

May 18th, 2002, 01:43 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Where's the "any" key ? (_8 ()

May 18th, 2002, 01:49 PM	#3
stevebausch Board Regular Join Date: May 2002 Posts: 809	If you had a network folder accessible to all, you could send a shortcut to the file to everyone, and just let them load the new version to their station. Before I continue to say things that may not be accurate/appreciated, keep in mind that I tend to take advantage of threads that have zero response 26 pages correspond to a year's worth of payroll, correct? And that's why it might be hard to swap out the workbook in the middle of the year? I know that what you ask (self-modifying code) can be done, just never done it. http://www.cgi101.com/~clr/vba/routines/selfmodify.html Take a look at this page, see if it helps you. Or perhaps this page: http://support.microsoft.com/default...;EN-US;q219905 [ This Message was edited by: stevebausch on 2002-05-18 13:15 ]