Excel/Windows 2000 - Default footer

[Freckles]

My boss has asked me to set up a default footer for all members of his department. I plan on doing this by designing a book.xlt and sheet.xlt (for new workbooks and inserted worksheets) and having them place it in C:\Program Files\Microsoft Office\Office\Xlstart on their computer. But I have a few problems/Questions about the left footer in particular.

1. I know that I can easily add the workbook name, BUT I also need to add the path. Microsoft Knowledge Base Article - 213615 gives the following macro to add the path

Sub UpdateFooter()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End Sub

BUT, I need this macro to run automatically when they open a new workook. Where do I tell them to put it? Or can I put it in the template? But how do I get it to run automatically?

2. Once I get that figured out, I need to put together a macro with very specific instructions on installation that would add the same footer to all of their "old" documents. However, most of the people I am dealing with look at a macro as being beyond their comprehension and totally "gobbly-gook". So I need to give them a macro that "maybe" automatically places a button in their toolbar and hope they remember to run it when they open an "old" workbook".

3. In the best of all possible worlds, there would be a way that when any "old" workbook was opened a macro would run that automatically formatted the footer for all sheets -- but I think that is dreaming

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Freckles]

I have made some progress after my last posting!

I think that if I put the following code in the xlt files, it should be able to handle the New workbooks and worksheets


Private Sub Workbook_Open()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
ActiveSheet.PageSetup.CenterFooter = "Confidential"
ActiveSheet.PageSetup.RightFooter = Date
End Sub


I still don't have a clue how to handle current workbooks/sheets.

But how do I add sheet name to the LeftFooter and Time to the RightFooter???

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[Freckles]

I now know that my method above works!! But in addition to addiding the time to the rightfooter, I need to know how to make the CenterFooter have two rows.

BUT the biggest problem is how I can get the same template to open when someone hits the "NEW" icon versus doing "File - NEW" and choosing the "book" template. Any ideas.

: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[jblewis]

Well, I'm looking for for the same sort of info, but I think I might be able to help you on some of the things.

As far as two lines in the footer try:
.leftfooter = "blah" & vbCRLF & "foo"

Have you tried ?
Private Sub Workbook_New()

Or try a separate module called "NEW" and have main() contain your code. I do something similar to that with Word and SAVE and SAVEAS

I'll be working on the same sort of thing, so if make any headway, I'll let you know!

JB