Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
The first Visual Basic code sample shows the results of recording a Visual
Basic
PageSetup function.
The second Visual Basic code sample shows one way in which you can prevent
the screen from flickering or blinking while a
PageSetup function is being executed.
Example One - Recording a Page Setup
- Create a new workbook.
- Activate a worksheet in the workbook.
- On the Tools menu, point to Macro, and click
Record New Macro.
- In the Store macro in list, make sure
This Workbook is selected.
- Click OK to begin recording.
- On the File menu, click Page Setup.
- In the Page Setup dialog box, click OK.
- On the Tools menu, point to Macro, and then click
Stop Recording.
- Activate the new Visual Basic module. Your recorded subroutine
should appear similar to the following (comments have been added for
explanation--they are not actually recorded).
Sub Macro1()
With ActiveSheet.PageSetup ' This is the first part.
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "" ' This is the second part.
With ActiveSheet.PageSetup ' This is the third part.
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With
End Sub
When the
PageSetup function is recorded, the settings are recorded in three parts:
- The first part is a With-End With section which sets the
PrintTitleRows and the PrintTitleColumns.
The second part sets the PrintArea.
The third part is a With-End With section, which sets all of the
other settings.
- The second part sets the PrintArea.
The third part is a With-End With section, which sets all of the
other settings.
- The third part is a With-End With section, which sets all of the
other settings.
If you do not actually want to change certain settings, such as
.Draft, you can remove those lines from the subroutine. For example, if you only want to change the
PrintTitleRows, the
PrintArea, and the
Orientation, you could use the following:
Sub Macro1()
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3"
.PrintArea = "$A$4:$C$100"
.Orientation = xlLandscape
End With
End Sub
Because the other settings do not need to be changed, it is not necessary
to include them in the subroutine. However, you must remove them yourself.
Also, note that the entire
PageSetup procedure can be incorporated into a single
With-End With section. It is not necessary for the
PrintArea,
PrintTitleRows, or
PrintTitleColumns settings to be changed separately from the other settings; it is only recorded that way.
Example Two - Preventing Screen Flicker While PageSetup Executes
The following subroutine demonstrates one way in which you may prevent the
screen from flickering while a
PageSetup function is being executed.
Sub PreventScreenFlicker()
' This line turns off screen updating.
Application.ScreenUpdating = False
' Apply each of the following properties to the active sheet's Page
' Setup.
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$3" ' Set print title rows.
.PrintTitleColumns = "$A:$C" ' Set print title columns.
.LeftHeader = "" ' Set the left header.
' More commands could appear before the End With. They are not
' shown here in order to keep the example short.
End With ' End of With section.
' Re-enable screen updating. This line is optional; you may not need
' or want to re-enable screen updating.
Application.ScreenUpdating = True
End Sub
The subroutine turns off screen updating just before executing the
PageSetup function and then turns screen updating back on when the
PageSetup function is complete.
If screen updating is not turned off, as each line in the
With section (
.PrintTitleRows,
.PrintTitleColumns, and so forth) is executed, the screen may flicker slightly.