ActiveSheet.PageSetup

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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...<pre>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</pre>

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
 
Upvote 0
Thanks for the replies. I'll definitely take a look at this.
 
Upvote 0
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).
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top