XL2000: All PageSetup Settings Are Recorded into Macro

• Microsoft Excel 2000

SYMPTOMS

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 are recorded.

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.

CAUSE

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.

WORKAROUND

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

Example One - Recording a Page Setup

1. Create a new workbook.



2. Activate a worksheet in the workbook.



3. On the Tools menu, point to Macro, and click Record New Macro.



4. In the Store macro in list, make sure This Workbook is selected.



5. Click OK to begin recording.



6. On the File menu, click Page Setup.



7. In the Page Setup dialog box, click OK.



8. On the Tools menu, point to Macro, and then click Stop Recording.



9. 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 

1. The first part is a With-End With section which sets the PrintTitleRows and the PrintTitleColumns.
   
   
2. The second part sets the PrintArea.
   
   
3. The third part is a With-End With section, which sets all of the other settings.

   Sub Macro1()
       With ActiveSheet.PageSetup
           .PrintTitleRows = "$1:$3"
           .PrintArea = "$A$4:$C$100"
           .Orientation = xlLandscape
       End With
   End Sub 

Example Two - Preventing Screen Flicker While PageSetup Executes

   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 

Additional query words: 9.00 XL2000

Keywords : kbprg kbdta kbdtacode PgmOthr KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb