In Microsoft Excel, if you record a Microsoft Visual Basic for Applications
macro that includes a Page Setup command, all of the Page Setup settings
Because of this, you may find that running a recorded Visual Basic
PageSetup function may take an unusually long amount of time, up to two
minutes or more (depending on the speed of your computer). Also, the screen
may flicker or blink repeatedly while the function is being executed.
If you record a Page Setup in a Visual Basic macro, all the settings are
recorded due to the way in which page setup information is returned to the
macro recording system.
The flickering occurs due to the way in which the PageSetup function
updates the sheet's different Page Setup settings. The amount of flickering
is related to the number of Page Setup settings you change with the
PageSetup function: changing more settings results in more flickering.
After you record a Visual Basic PageSetup function, you will probably want
to eliminate unneeded settings from the PageSetup function.
To prevent the flickering, set the Application.ScreenUpdating property to
False before executing your PageSetup function. Then, when the PageSetup
function has completed, you can set the Application.ScreenUpdating property
back to True to reenable screen redraws.
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 example shows the results of recording a Visual
Basic PageSetup function.
The second Visual Basic code example shows one way in which you can prevent
the screen from flickering or blinking while a PageSetup function is being
Example One - Recording a Page Setup
- Create a new workbook.
- Activate a worksheet in the workbook.
- On the Tools menu, point to Record Macro, and click Record New Macro.
In Microsoft Excel 97 and Microsoft Excel 98, click the Tools menu,
point to Macro, and then click Record New Macro.
- In the Record New Macro dialog box, select the Options button. In the
language section, make sure that the "Visual Basic" option button is
selected. (Ignore this step in Microsoft Excel 97 and Microsoft Excel
- In the "Store in" section, make sure the "This Workbook" option button
- 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 Record Macro, and then click Stop
- 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).
With ActiveSheet.PageSetup ' This is the first part.
.PrintTitleRows = ""
.PrintTitleColumns = ""
ActiveSheet.PageSetup.PrintArea = "" ' This is the second part.
With ActiveSheet.PageSetup ' This is the third part.
.LeftHeader = ""
.CenterHeader = "&A"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.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 = True
.PrintNotes = False
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
' On a Macintosh computer, omit the following line.
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
When the PageSetup function is recorded, the settings are recorded in
- 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
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:
.PrintTitleRows = "$1:$3"
.PrintArea = "$A$4:$C$100"
.Orientation = xlLandscape
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.
' This line turns off screen updating.
Application.ScreenUpdating = False
' Apply each of the following properties to the active sheet's Page
.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
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.
Article ID: 109205 - Last Review: June 23, 2005 - Revision: 2.0
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 98 for Macintosh
|kbdtacode kbhowto kbprogramming KB109205|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.