MeadInKent
| Feedback | Keyboard shortcuts | Hyperlinks in spreadsheets |

Applying a standard print page setup for Excel - a macro and toolbar button

This section illustrates a simple macro that can be placed in your Personal.xlsb workbook and is then available to add to a button on your quick access toolbar (or a ribbon). The macro can add a simple page footer to the current worksheet and then open the Print Preview box.

There are a large number of formatting options for headers and footers. If you record your own macro you can replace some or all of the code below. Simply modify or delete the elements which are not required.

Sub MyPageSetup()
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = _
"&8MeadInKent.co.uk" & Chr(10) & "Excel Functions"
.CenterFooter = "&8Page &P of &N"
.RightFooter = "&8&T &D" & Chr(10) & "&F / &A"
.LeftMargin = Application.InchesToPoints(0.74803)
.RightMargin = Application.InchesToPoints(0.74803)
.TopMargin = Application.InchesToPoints(0.59055)
.BottomMargin = Application.InchesToPoints(0.787401)
.HeaderMargin = Application.InchesToPoints(0.511811)
.FooterMargin = Application.InchesToPoints(0.511811)
.PaperSize = xlPaperA4
End With
ActiveWindow.SelectedSheets.PrintPreview
End Sub

Excel allows you to customise your ribbons and the quick access toolbar and add buttons for useful procedures and options. A macro can be linked to a new button and it then becomes available whenever Excel is opened.

Open the VBA editor ([Developer] Visual Basic Editor). Select the Personal.xlsb VBA Project and Insert | Module. Either type your own code or copy the code from this page into the module to create a new procedure called MyPageSetup().

Close the VBA editor and return to Excel. Click the small down arrow on the right hand end of the Quick Access toolbar and choose More Commands...

The Excel Options dialog box will appear and has a tab for either customising a ribbon or the [1] Quick Access toolbar. From the drop down box choose commands from [2] Macros. The macros saved in your Personal workbook will be listed. [3] Choose one of them and [4] [Add>>] it to the toolbar.

To associate the macro with a button, click on [5] [Modify...] and choose an icon picture.


customise a toolbar button

Would you like to learn more?

book

Step by Step Excel 2010

Five keyboard short cuts

excel help Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlnextwindow.htm MeadInKent.co.uk 2014 Last updated Feb14CMIDX S7 P3 Y