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 will 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.
.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
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  Quick Access toolbar. From the drop down box choose commands from  Macros. The macros saved in your Personal workbook will be listed.  Choose one of them and  [Add>>] it to the toolbar.
To associate the macro with a button, click on  [Modify...] and choose an icon picture.
Would you like to learn more?
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xlnextwindow.htm||© MeadInKent.co.uk 2013||Last updated Feb13||CMIDX S7 P3 Y|