Can I permanently change Excel page margins? 2

[creeker69]

Hello and Happy Holidays,

I change the margins on almost every Excel sheet I produce. The default is for a 1" header, 1" footer and .75" right and left margins. I am constantly previewing and changing these to maximize my display of data on a single sheet.

Is there a way to have Excel recognize my preference and retain the settings the way is does with my preferred font, number of sheets in workbook, etc.

thanks in advance,
creeker

 

[KenWright]

Sure, if you haven't told Excel otherwise it will use it's own defaults, so all you have to do is tell it different.

Using a blank file, get it just the way you want it, setting all your column widths, page margins, footers headers etc. Now save it as Book.xlt (Choose templates from the 'save as' dropdown) and save it in your XLSTART folder. It may try to name it Book1.xlt but it must be Book.xlt

Same thing for Sheet templates for the 'Insert New Sheet' option. Save a file with a single sheet, set just as you want and save it in the same place as Sheet.xlt

Get fed up with it, just delete it and it will revert back to the original template.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[DaleWatson123321]

Ken,

Excellent response. I had forgotton about the use of templates. Thanks for the reminder, and a STAR to you - well deserved.

While you were responding, I was "crafting" my own response, which takes a different approach. Naturally, YOUR solution is the best for the Page Settings, but having written my response, I feel I should still submit it - because, as I'm sure you'll appreciate, there are "other" benefits to be gained by Excel users in adopting the use of "hotkey" solutions - for many other routine tasks that collectively can "slooooooow" a user down, as compared to the increased "speeeeeeeeeed" of using hotkeys

Here's my "unedited" version...

Happy Holidays also

It's possible to change Page-Settings via VBA code attached to a macro button which you can add to an existing toolbar. You can obtain the VBA code from recording a macro. Or, if you like, copy the following example, but you'll see there are additional settings (which you might want to include) when you record the macro.

After adding the macro button, most users will add a "hotkey" to the button, so that you only need to hold down the <Alt> key and hit whatever letter or number you assign to the button. After the code below, I'll provide the steps to follow in setting up the macro button.

The code needs to be placed into your "PERSONAL.xls" file - which should be in your "XLStart" folder. Depending on your version of Excel, it could be located in a different location. The following is from the Help function of Excel 2002... "You can find XLStart folders in C:\Documents and Settings\username\Application Data\Microsoft\Excel or in C:\Program Files\Microsoft Office\Office10." On some networks, the network administrator will have perhaps locked out users from access to this folder. If so, an option is to use the following... another excerpt from Help...

Open all files in a folder when Excel starts...
Caution: Because Excel will attempt to open every file in the additional startup folder, make sure you specify an empty folder or a folder that contains only files that Excel can open.

On the Tools menu, click Options, and then click the General tab. In the At startup, open all files in box, type the path to the additional startup folder. Note: If a file with the same name is in both the XLStart folder and the additional startup folder, the file in the XLStart folder opens.

Here's the VBA code for setting Page Settings...

Sub Set_PageSettings()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.35)
.FooterMargin = Application.InchesToPoints(0)
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
End With
Application.ScreenUpdating = True
End Sub

STEPS TO USE IN SETTING UP A MACRO BUTTON...

1) Right-click anywhere on the top (toolbar) part of the screen.

2) Choose "Customize" (bottom of the dropdown list).

3) In the "Customize" window, click on the "Commands" tab.

4) On the left, under the "Categories" heading, scroll down and click on "Macros".

5) On the right, under the "Commands" heading, click-and-drag the " Custom Button" to one of your existing toolbars. You can place it on the Main toolbar for example, to the right of "Help".

6) While still in the "Customize" window, right-click on the macro icon you just placed on your toolbar - the icon.

7) Opposite "Name" (3rd from the top of the dropdown list), enter: &_ ...where you replace the "_" character with a suitable letter or number. This will become the character or number you use to activate the macro when you hold down the <Alt> key. (The "&" character is a special character that causes the letter following it to become a hotkey.) Naturally, you do NOT want to choose a letter that is ALREADY in use - for example any of the existing "hotkey" letters (those letters that are underlined) on the toolbars.

8) IMPORTANT - From the same dropdown list, click on "Text Only (Always)". This will cause the icon to change from to the text you specified opposite "Name".

9) Click "Close" to exit out of the "Customize" window, and you're done.

IMPORTANT: Don't forget to save your "PERSONAL.xls" file


RECOMMENDATION... Now that you've learned the process of setting up a macro and hotkey, I'd strongly recommend you use this "new power" to set up additional "hotkeys" for those tasks you find yourself using often. I have "many" shortcuts on my toolbars, but here are a couple of examples I use...

For setting the HEIGHT of row(s), I use <Alt> H.
For setting the WIDTH of column(s), I use <Alt> W.

For more than one row or column, I simply hold down <Shift> while I highlight the range, and then use the hotkey.

NOTE: In order to use "H" and "W", you'll need to eliminate the "&" from their default location in the MAIN toolbar - for "Window" and "Help". I don't feel I need a hotkey for either Window or Help. <F1> works for Help, and for Window, I can force myself to use the mouse to click on this menu item.

To find the icons for HEIGHT and WIDTH, use the same process as described above (Steps 1 through 9), but in this case, choose "Format" under the "Commands" tab and "Categories" heading. Then on the right-side under "Commands" heading, the icons are identified as "Height..." and "Width...".

Finally, here's one last recommendation for a hotkey - to Align Text Across Columns. In most cases, this is preferable to Merging cells - mainly because of not being able to insert columns where cells are merged, and having the cursor "jump around" when moving over merged cells. I find this hotkey function (Aligning Text Across Columns) especially important because there's no icon for it ...VBA code is used - see below. I use <Alt> A, which I find is MUCH faster than using the menu method - which is... 1) Format - Cells (or <Ctrl> 1 will also bring up the "Format Cells" window), 2) click the "Alignment" tab, 3) Under Text alignment - Horizontal, click the pulldown-triangle icon (opposite "General"), 4) and finally... click on "Center Across Selection." Naturally, for this function, you need to FIRST highlight the selection - i.e. range of cells over which you want the contents centered.

Here's the VBA code you'll need to include in your PERSONAL.xls file and then assign to a macro-button icon, using the same 1 - 9 steps, except of course you'll want to assign a different letter - such as A - for align.

Sub AlignAcrossColumns()
With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.WrapText = True
End With
End Sub

I'm confident that once you "take the first bite" (of this hotkey process), you'll become addicted - because of the realization that many routine tasks can be made MUCH easier with hotkeys.

I hope this helps.

Regards, Dale Watson