Solved

Excel 2010 Header/Footer scale with document

Posted on 2011-03-07
19
1,449 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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
I think the personal workbook always opens but it is hidden.

Curt
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 18

Expert Comment

by:Curt Lindstrom
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

10 Experts available now in Live!

Get 1:1 Help Now