Results 1 to 6 of 6

ActiveSheet.PageSetup

This is a discussion on ActiveSheet.PageSetup within the Excel Questions forums, part of the Question Forums category; Hello All, I've got a macro that formats data. It use to run fine. However, the times they have a ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    384

    Default

    Hello All, I've got a macro that formats data. It use to run fine. However, the times they have a changed on me. I've pasted a section of code below(it sets my printer orientation). Without this code my macro will run fine and quick. With it my macro will still run, but it creates a white screen of death for about 25 seconds(slow) and the task manager shows that the sheet and the VB session are "not responding". Has anyone run into this before? I stepped through the code using the debugger and it is this code that causes the whole thing to hang. (Each line of code produces a hour glass)
    Keep in mind the code ran fine for months. I read somewhere that macros eventual get fragmented on the drive and thus experience decreased performance. I pasted the code into a new module thinking that it would be stored non fragmented and thus run (no luck). I have even re-recorded the code and it still hangs when it comes to activesheet.pagesetup.

    'PRINTER ORIENTATION
    Date = Format(Date, "mm/dd/yy")
    polno = Range("A2").Value
    insnm = Range("G2").Value
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    .PrintArea = ""
    .LeftHeader = LH ' Variable from frmSendTo
    .CenterHeader = "&"",Bold"" POLICY LOSS RUN" & Chr(10) & "Policy Number: " & polno & Chr(10) & "Insured Name: " & insnm
    .RightHeader = "Valuation Date: " & Date
    .LeftFooter = ""
    .CenterFooter = "Page &P of &N"
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0.92)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    End With

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,163

    Default

    Hi Marc,

    Chances are your problem has nothing to do with fragmentation, or even VBA for that matter. One of the little-known aspects of modifying properties of the PageSetup object is that EACH property setting generally has to be individually checked with the printer driver. If your printer is directly connected to your computer this happens very fast--and usually reliably. If the printer is on a network print server, however, the type of problem you are experiencing can occur if the network is overloaded with traffic, if the print server "hangs", or if numerous other network/printer/driver problems occur. At the very least, with a network printer you can expect the PageSetup property settings to run very slowly because of this "one at a time" verification.

    Check to make sure you can print from other applications to the same printer. Also, try switching to another printer, and especially to a local printer to see if the problem is related to a particular printer/driver.
    Finally, to speed up the settings, reduce the number of property settings to an absolute minimum. Don't set properties to values that are the same as their default values.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm
    (My other life: http://damonostrander.com )

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    I am in full agreement with Damon on this one for his recommendations.

    The fastest way, that I am aware of, to set the page setup properties is to use an Excel4 macro.

    Here is an example from some code I use...

    HeaderString = Application.UserName & vbCr & Consolidated_Account_Name & _
    vbCr & "From: " & From_Date & " " & "Through: " & _
    Through_Date & vbCr & FeeType & " / " & Weighting & vbCr & Date

    FooterString = "Page &P of &N"

    Hstring = """&R" & HeaderString & """"
    Fstring = """&R" & FooterString & """"

    wks2.Select
    Application.ExecuteExcel4Macro ("PAGE.SETUP(" & Hstring & _
    "," & Fstring & ",0.5,0.5,1.1,0.25,,True,,,2,,,,,,,0,0.1)")
    With wks2.PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = "$A:$A"
    .Zoom = 80
    End With


    I only use the VBA .PageSetup when I have to. The others are in the "PAGE.SETUP" part.

    Note that these parameters are specified in a particular order.

    To find all the arguments, download the macrofun.hlp file from the MS site (address not available off hand).

    Below is the help contents for the PAGE.SETUP function...

    Code:
    Macro Sheets Only
    Equivalent to choosing the Page Setup command from the File menu. 
    Use PAGE.SETUP to control the printed appearance of your sheets.
    There are three syntax forms of PAGE.SETUP. Syntax 1 applies if a sheet or macro sheet is active; 
    syntax 2 applies if a chart is active;
    syntax three applies to Visual Basic modules and the info Window.
    Arguments correspond to check boxes and text boxes in the Page Setup dialog box. 
    Arguments that correspond to check boxes are logical values. 
    If an argument is TRUE, Microsoft Excel selects the check box; if FALSE, Microsoft Excel clears the check box. 
    Arguments for margins are always in inches, regardless of your country setting.
    
    Syntax 1
    
    Worksheets and macro sheets
    PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, _
    scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)
    PAGE.SETUP?(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, _
    pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)
    
    Syntax 2
    
    Charts
    PAGE.SETUP(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, _
    pg_num, bw_chart, quality, head_margin, foot_margin, draft)
    PAGE.SETUP?(head, foot, left, right, top, bot, size, h_cntr, v_cntr, orient, paper_size, scale, _
    pg_num, bw_chart, quality, head_margin, foot_margin, draft)
    
    Syntax 3
    
    Visual Basic Modules and the Info Window
    PAGE.SETUP(head, foot, left, right, top, bot, orient, paper_size, scale, _
    quality, head_margin, foot_margin, pg_num)
    PAGE.SETUP?(head, foot, left, right, top, bot, orient, paper_size, scale, _
    quality, head_margin, foot_margin, pg_num)
    Head    specifies the text and formatting codes for the header for the current sheet. 
    
    For information about formatting codes, see "Remarks" later in this topic.
    Foot    specifies the text and formatting codes for the workbook footer.
    
    Left    corresponds to the Left box and is a number specifying the left margin.
    
    Right    corresponds to the Right box and is a number specifying the right margin.
    
    Top    corresponds to the Top box and is a number specifying the top margin.
    
    Bot    corresponds to the Bottom box and is a number specifying the bottom margin.
    
    Hdng    corresponds to the Row & Column Headings check box. 
    Hdng is available only in the sheet and macro sheet form of the function.
    
    Grid    corresponds to the Cell Gridlines check box. 
    Grid is available only in the sheet and macro sheet form of the function.
    
    H_cntr    corresponds to the Center Horizontally check box in the Margins panel of the Page Setup dialog box.
    
    V_cntr    corresponds to the Center Vertically check box in the Margins panel of the Page Setup dialog box.
    
    Orient    determines the direction in which your workbook is printed.
    
    Orient	Print format
    
    1	Portrait
    2	Landscape
    
    Paper_size    is a number from 1 to 26 that specifies the size of the paper.
    
    Paper_size	Paper type
    
    1	Letter
    2	Letter (small)
    3	Tabloid
    4	Ledger
    5	Legal
    6	Statement
    7	Executive
    8	A3
    9	A4
    10	A4 (small)
    11	A5
    12	B4
    13	B5
    14	Folio
    15	Quarto
    16	10x14
    17	11x17
    18	Note
    19	ENV9
    20	ENV10
    21	ENV11
    22	ENV12
    23	ENV14
    24	C Sheet
    25	D Sheet
    26	E Sheet
    
    Scale    is a number representing the percentage to increase or decrease the size of the sheet. 
    All scaling retains the aspect ratio of the original.
    
    To specify a percentage of reduction or enlargement, set scale to the percentage.
    	
    For worksheets and macros, you can specify the number of pages that the printout should be scaled to fit. 
    Set scale to a two-item horizontal array, with the first item equal to the width and
    the second item equal to the height. 
    If no constraint is necessary in one direction, you can set the corresponding value to #N/A.
    	Scale can also be a logical value. To fit the print area on a single page, set scale to TRUE.
    
    Pg_num    specifies the number of the first page. 
    If zero, sets first page to zero. If "Auto" is used, then the page numbering is set to automatic. 
    If omitted, PAGE.SETUP retains the existing pg_num.
    Pg_order    specifies whether pagination is left-to-right and then down, or top-to-bottom and then right.
    
    Pg_order	Pagination
    
    1	Top-to-bottom, then right
    2	Left-to-right, then down
    
    Bw_cells    is a logical value that specifies whether to print cells and all graphic objects, 
    such as text boxes and buttons, in color.
    
    If bw_cells is TRUE, Microsoft Excel prints cell text and borders in black and cell backgrounds in white.
    	If bw_cells is FALSE , Microsoft Excel prints cell text, borders, and background patterns in color (or in gray scale).
    
    Bw_chart    is a logical value that specifies whether to print chart in color. 
    Size    is a number corresponding to the options in the Chart Size box, 
    and determines how you want the chart printed on the page within the margins. 
    Size is available only in the chart form of the function.
    
    Size	Size to print the chart
    
    1	Screen size
    2	Fit to page
    3	Full page
    
    Quality    specifies the print quality in dots-per-inch. 
    To specify both horizontal and vertical print quality, use an array of two values.
    Head_margin    is the placement, in inches, of the running head margin from the edge of the page.
    Foot_margin    is the placement, in inches, of the running foot margin from the edge of the page.
    Draft    corresponds to the Draft Quality checkbox in the Sheet tab and in the Chart tab of the Page Setup dialog box. 
    If FALSE or omitted, graphics are printed with the sheet. 
    If TRUE, no graphics are printed.
    Notes    specifies whether to print cell notes with the sheet. 
    If TRUE, both the sheet and the cell notes are printed. 
    If FALSE or omitted, just the sheet is printed.
    
    Remarks
    
    Microsoft Excel no longer requires you to enter formatting codes to format headers and footers, 
    but the codes are still supported and recorded by the macro recorder. 
    You can include these codes as part of the head and foot text strings to align portions of the header or footer to the left, 
    right, or center; 
    to include the page number, date, time, or workbook name; 
    and to print the header or footer in bold or italic.
    
    Formatting code	Result
    
    &L	Left-aligns the characters that follow.
    &C	Centers the characters that follow.
    &R	Right-aligns the characters that follow.
    &B	Turns bold printing on or off (now obsolete).
    &I	Turns italic printing on or off.
    &U	Turns single underlining printing on or off.
    &S	Turns strikethrough printing on or off.
    &O	Turns outline printing on or off (Macintosh only).
    &H	Turns shadow printing on or off (Macintosh only).
    &D	Prints the current date.
    &T	Prints the current time.
    &A	Prints the name of the sheet
    &F	Prints the name of the workbook.
    &P	Prints the page number.
    &P+number	Prints the page number plus number.
    &P-number	Prints the page number minus number.
    &&	Prints a single ampersand.
    
    & "fontname, fontstyle"	Prints the characters that follow in the specified font and style. 
    Be sure to include a comma immediately following the fontname, and double quotation marks around fontname and fontstyle.
    &nn	Prints the characters that follow in the specified font size. Use a two-digit number to specify a size in points.
    &N	Prints the total number of pages in the workbook.
    &E	Prints a double underline
    &X	Prints the character as superscript
    &Y	Prints the chararcter as subscript
    
    Related Functions
    Note also that MS does not support these in full any longer, and this option may be unavailable in future versions, but using these *will* speed your code.

    _________________
    Bye,
    Jay

    [ This Message was edited by: Jay Petrulis on 2002-08-21 12:03 ]

  4. #4
    Board Regular XL Pro's Avatar
    Join Date
    Apr 2002
    Location
    =VLOOKUP("XL Pro",Earth,1,FALSE)
    Posts
    206

    Default

    Thanks for the replies. I'll definitely take a look at this.
    Not as Lean
    Not as Mean
    But still a Marine
    Semper Fi

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    384

    Default

    Thanks for the help everyone. I just got a chance to incorporate the page.setup macro and it has cut off a considerable amount of time. I just have to work out some more of the parameters to tweak it.
    Now that I recall it, my network printers were recently reorganized(seems like there's a lot more in the add printer dialog box).

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Default Re: ActiveSheet.PageSetup

    I was looking for something else and saw this thread...
    This may be of some help to someone else...
    My solution:

    Sub CoverRangeWithAChart()
    Dim RngToCover As Range
    Dim ChtOb As ChartObject
    Dim endCOL = FindEndColumn
    Dim endROW = FindEndRow

    Set RngToCover = ActiveSheet.Range("A1:" & endCOL & endROW)
    Set ChtOb = ActiveChart.Parent

    ChtOb.Height = RngToCover.Height ' resize
    ChtOb.Width = RngToCover.Width ' resize
    ChtOb.Top = RngToCover.Top ' reposition
    ChtOb.Left = RngToCover.Left ' reposition
    End Sub

    where FindEndCol finds your last visible column on full page and
    FindEndRow finds your last visible row on full page
    (depends on row and column height)
    For me, a row height of 15 yeilds number of rows = 27
    and the number of columns = number of chart columns divided by 1.8
    Your mileage might vary - also added about 20 to the number of chart
    columns to account for legend and vertical axis space

    Hope this is helpful to someone...

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com