Default Workbook

[Insurance Daddy-O]

How to add header/footer to the default workbook that appears when you open
Excel? Eg. want to add file name and date as footers to all files created
without having to remember to add them for each workbook created.

 

[Gord Dibben]

Open a new workbook. Customize as you wish. To set a footer/header for each
sheet, select a sheet then right-click and "select all sheets".

Enter your footer on the active sheet and will be done to all. Ungroup sheets
when done.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP

On Fri, 25 Jan 2008 12:06:03 -0800, Insurance Daddy-O <Insurance

 

[Pugs]

I would just like clarification. If we were to have a branded workbook
template available for our consultants to download and use at a client site,
in order for new sheets to be inserted into the template with our branding in
the header/footer, they would need to download and save SHEET.XLT into the
XLSTART folder of whatever machine they're located at?

Is there any other way to have that branded header/footer show up in newly
inserted sheets without overwriting the SHEET.XLT on the machine they're at?
Our consultants often get assigned a client owned computer when they are at
client sites.

 

[Gord Dibben]

Yes, you would have to have your personalized SHEET.XLT in the Xlstart folder.

Have you thought about a macro in the Branded Workbook Template to insert a new
Excel default sheet and code to add the Header/Footer customization to that
newly inserted sheet?

Your consultants would then only need the Branded Workbook Template.

Any workbook based upon the Template would have the macro.


Gord

 

[Pugs]

Gord,

Thanks for the quick reply. I have thought about using macros, but
unfortunately that's a little beyond my knowledge level. At least one of the
consultants has to have that skill set, though, so I'll be checking with
them.

Thanks again.

 

[Pugs]

Okay,

I've figured out the how to create the macro. The problem that I'm
having now is that in the custome header and custom footer we have an image
with our company logo (header) and an image with our company slogan (footer).


I'm having difficulty figuring out how to have the macro add the images.

 

[Dave Peterson]

Have you thought of including a worksheet (hidden) in that workbook and
providing a macro that would create a new (visible) worksheet based on that
hidden sheet?

 

[Pugs]

Dave,

This is an excellent solution! I'm playing with it right now. My only
complaint so far is I'm trying to figure out how to have newly created sheet
moved to be the last sheet.

With the sheet.copy function, you have to specify a sheet for it to be
inserted before or after. If you tell it to insert after Sheet3 (let's say
that this is the last sheet in the book), it will always insert after Sheet3.
So you insert one sheet new sheet and it inserts after Sheet3. You insert
another sheet and it inserts after Sheet3 again, but before the one the
previously inserted. Did that make sense?

 

[Pugs]

Nevermind,

I've sorted it out. Right now, my macros look like:

Sub NewSheet()
Sheets("Sheet").Select
Sheets("Sheet").Copy After:=Sheets(Sheets.Count)
End Sub

Sub HideSheetTemplate()
Sheet1.Visible = xlVeryHidden
End Sub

Sub ShowSheetTemplate()
Sheet1.Visible = True
End Sub

They're working beautifully. See! Sometimes us non-technical Project
Managers can accomplish something slightly technical!

 

[Gord Dibben]

Excellent and I am happy Dave came up with a better solution with the hidden
worksheet.


Gord

 

[Pugs]

Hey,

I appreciate your input and responding so quickly. That sent me down
the macro path so Dave's suggestion dovetailed nicely.

In case it will help anyone else, I had to modify the macro slightly.
I found that when the worksheet is hidden, the 'Sheets("Sheet").Copy
After:=Sheets(Sheets.Count)' function errors out because it cannot find the
hidden sheet. To modify it, I've added code to un-hide the sheet before
copying it, and then hiding it again after copying it.

The scripts now look like this:

Sub NewSheet()
Sheet1.Visible = True
Sheets("Sheet").Select
Sheets("Sheet").Copy After:=Sheets(Sheets.Count)
Sheet1.Visible = xlVeryHidden
End Sub

Sub HideSheetTemplate()
Sheet1.Visible = xlVeryHidden
End Sub

Sub ShowSheetTemplate()
Sheet1.Visible = True
End Sub

 

[Dave Peterson]

If you add:

application.screenupdating = false
your code to unhide, copy, and hide
application.screenupdating = true

You won't see the flickering.

And you could drop this line:
Sheets("Sheet").Select

You didn't do anything once you selected it.

 

[Pugs]

Roger that.

Thank you!