Solved

Excel 2010 Header/Footer scale with document

Posted on 2011-03-07
19
1,474 Views
Last Modified: 2012-05-11
Hello,

I have a user who would like for the "Scale with document" option, under header/footers, to be unchecked by default in Excel 2010.  We've resolved the issue for new Excel spreadsheets by adding a template to the Excel startup folder with that option unchecked.  However, when opening spreadsheets created from older version of Excel, the "Scale with document" option is checked by default.  If we uncheck and then save the document, the option sticks.  What we'd like to know is, is there a way to default this option off all of the time?
0
Comment
Question by:apextmi
  • 7
  • 6
  • 4
  • +1
19 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 35063258
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35063301
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35063325
@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
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063373
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
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063385
I think the personal workbook always opens but it is hidden.

Curt
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063406
@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
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35063418
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

Open 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
0
 
LVL 50

Accepted Solution

by:
teylyn earned 400 total points
ID: 35063541
apextmi,

what you can do is to include the macro below in a standard module in the user's Personal.xlsb, and then upon opening an existing workbook, run the macro. You can tie the macro to a keyboard shortcut, so the user opens a file, hits the keyboard shortcut and the setting will be applied to each sheet in the file.

Option Explicit

Sub ScaleHeaderFalse()
Dim worksheet As worksheet
For Each worksheet In ActiveWorkbook.Worksheets
    With worksheet
       .PageSetup.ScaleWithDocHeaderFooter = False
    End With
Next
End Sub

Open in new window


cheers, teylyn
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063710
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35063987
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
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35064000
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.

0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35064157
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35065901
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.
0
 
LVL 50

Expert Comment

by:teylyn
ID: 35065975
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
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35066050
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.
0
 

Author Comment

by:apextmi
ID: 35085667
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.
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 100 total points
ID: 35085841
At this point, you need to hide the personal workbook so it doesn't load each time, but it applies its settings.

To do that, go to the view tab and select Hide.  You should see personal workbook as one of the choices.  Select it and it should now be hidden for future loads to excel.
0
 

Author Closing Comment

by:apextmi
ID: 35086199
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.
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35089247
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now