Excel 2010 Header/Footer scale with document

Hello,

I don't think you can do that. It is an option that is saved with the file, with each sheet, actually, so the workbook will open with whatever setting it was last saved with.

It might be possible to run a macro over all files in a folder, using

.ScaleWithDocHeaderFooter = False

for the Sheet.PageSetup of each sheet. This is basically automating the process you are doing manually now. But if your files are distributed across the company, I guess it would be very complex to set up and identify the files to be processed.

cheers, teylyn

I did some checking, and it can be done in a bit of a tricky way.

If you open your PERSONAL.xlsb workbook, you can add this code and it should force it to stay checked for the local machine.

Private Sub Workbook_Open()
   ActiveSheet.PageSetup.ScaleWithDocHeaderFooter = True
End Sub

This will have to be put into every machine where you want it to be the default, but it should be retained with every workbook you create even if sent to another machine without that setting.

If you need directions on how to unhide/hide your PERSONAL workbook, let me know.

@rspahitz,

I'm not so sure this will work. The Private Sub Workbook_Open will fire when the Personal.xlsb is opened. It will do nothing if you then open any other file.

Correct me if I'm wrong.

Also, according to the question, the setting should be = FALSE ("the "Scale with document" option, under header/footers, to be unchecked by default ")

cheers, teylyn

Enter this in the personal workbook Private Sub Workbook_Open

Private Sub Workbook_Open()
    For Each Worksheet In ActiveWorkbook.Worksheets
        With Worksheet
            .PageSetup.ScaleWithDocHeaderFooter = False
        End With
    Next
End Sub

Curt

I think the personal workbook always opens but it is hidden.

Curt

@rspahitz,

You need the loop to do the page setup for all sheets since page setup is set for each sheet and not for the whole workbook.

Curt

epaclm,

the Workbook_Open event will run when the personal.xlsb is opened. After that it will do nothing. It will not run when you open another file.

The personal.xlsb is just a file that sits in the XLSTART folder, and hence opens when Excel is started. Whether or not is hidden depends on whether or not it is set to be hidden. It does not have to be.

To prove that this does not work, I have this sub in my personal.xlsb:

Private Sub Workbook_Open()
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Select allOpen in new window

Now according to yours and rspahitz's suggestions, any file that I open with Excel should get A1 of the current sheet colored with a yellow background.

Nope, does not happen.

Again: the  Sub Workbook_Open() runs when the workbook it resides in is opened. It does not run when any other workbook is opened.

cheers, teylyn

I didn't suggest that a personal workbook opens for every Excel file you open but it opens every time you open the first Excel file with no other files open.

My macro in the personal workbook open is no good anyway. It only seems to work when you start a new workbook.

Curt

Another way of simplify this would be to add the macro to the Quick Access toolbar.

1.  Add my macro to a standard module as suggested by Teylyn
2. Open Excel Options>Customize
3. Change the top left Scroll bar to Macros
4. select the Personal.XLSB!ScaleHeaderFalse macro and press Add followed by OK

You can now use the macro directly from the Quick Access toolbar

Cheers,
Curt

Curt, the Sub Workbook_Open() will perform its job when personal.xlsb is opened. And it will run the code you posted on the sheets in the personal.xlsb.

Even if you double click a file to open Excel, and the personal.xlsb opens before the other file, then the macro will run on personal.xlsb, not the file that was double clicked. The Sub Workbook_Open() is the wrong tool for the job.

Teylyn,

I already agreed with you that the Sub Workbook_Open() is the wrong tool for the job and that it will only work for a new workbook.

My last post suggests to follow the line you suggested but to use the customize option to get a one click option for the user.

Cheers,
Curt

Curt, you are right that it needs to be done for each sheet.
Teylyn, I tried it and even after I closed all documents, when I re-opened it loaded the Personal workbook as the default, similar to how Word opens Normal.dot as the default document.
And yes, it should have been False like Curt indicated.

Teylyn wrote: "Curt, the Sub Workbook_Open() will perform its job when personal.xlsb is opened. And it will run the code you posted on the sheets in the personal.xlsb.

Even if you double click a file to open Excel, and the personal.xlsb opens before the other file, then the macro will run on personal.xlsb, not the file that was double clicked. The Sub Workbook_Open() is the wrong tool for the job. "

I found that if I *hid* the personal workbook, it applied its macros to all newly opened workbooks.  I didn't test it thoroughly in every condition; it certainly won't apply to existing workbooks. It also may not apply if you open new from Excel, but it worked fine when double-clicking the icon.

To confirm that, I put a messagebox in the open of the personal workbook and hid it and closed excel.  When I double-clicked the icon, excel opened a new workbook and I got the message.

rspahitz, for the sake of the exercise: My personal.xlsb has this:

Private Sub Workbook_Open()
MsgBox "hello world"
End Sub

I saved it. I have set personal.xlsb to hidden. I have closed Excel.

I open Excel, either from the Start button or by double clicking a file and "Hello world" comes up. No surprise, because it comes up when personal.xlsb loads.

From the File menu/tab I then open another existing workbook.

No "Hello World".

The Worbook_Open event fires only once: when personal.xlsb is loaded.

If you need a solution that is applied automatically to every workbook you open, this method fails.

q.e.d.

But meanwhile a solution has been found that can be executed with a keyboard shortcut or a mouse click.

No bad second choice, I feel.

cheers, teylyn

Thanks for checking teylyn.
It seems that the PERSONAL workbook opens once when the Excel application first opens, but not on subsequent opens while the application is open.

I think the shortcut key is a reasonable measure to ensure that the function is alway set as desired.  And since this can be put in the title bar, that makes it even easier, especially if you train users to press that as the "print" button where it sets this then opens the print dialog box.

Ok, first of all, sorry it took me so long to get back to this.  I didn't realize the alerts were coming into a different email address.  
Anyway, I'm a Network Engineer and NOT an Excel or Office expert.  Is there a way I could get a step-by-step on creating and saving the macro.  I got into the Personal.xlsb and added the macro that teylyn put up, but now when I open Excel, it opens the Personal.xlsb file (which means I have to change the file type and name each time I do a save).  Also, I created a test file and then ran the macro and it blanked my test file.  So, I'm not sure I'm doing this correctly.

The solution teylyn provided worked, I just didn't have the knowledge of Excel to utilize it fully.  Thanks rspahitz for pointing me in the right direction.

You win some and you lose some! I thought my contribution was a little bit more than zero, especially since it was my macro that was the one that worked in the end. It was just applied in a different module than first suggested.

Never mind, I'm glad you got things working now!

Curt