| Feedback | Keyboard shortcuts | Hyperlinks in spreadsheets | |
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.
![]() |
Get this information as a document accompanied by Excel worksheets |
![]() |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
file: xlnextwindow.htm | © MeadInKent.co.uk 2014 | Last updated Feb14 | CMIDX S7 P3 Y |