Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page Format with Macro (WIN2000-XL97)

    I have been able (thanks to Hans and other here) to come up with the following macro to
    format my spreadsheets.

    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .CenterFooter = Date
    .RightFooter = "&P"
    .FitToPagesTall = 20
    .FitToPagesWide = 1
    End With

    The piece that I am missing, and can't figure out, is that I would like the following:

    On the Page tab of Page Setup - Under Scaling - is have the "Fit to" radio button
    checked off. As you can see by the code above, I am able to set everything else.

    Can you please tell me what member of page setup controls the Fit to button?

    Thank you,

    Michael

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Try:

    <pre>With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .CenterFooter = Date
    .RightFooter = "&P"
    .FitToPagesTall = False
    .FitToPagesWide = False
    End With
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Thanks Legare. Actually, the code you wrote does the opposite!

    You see, the part:
    FitToPagesTall = 20
    .FitToPagesWide = 1
    is correct. I do want the Pages tall to be 20 because some spreadsheets could
    have 1000 lines.
    The code you wrote actually tics the "Scaling/Adjust to" button.
    I want the other button checked (Scaling/Fit to).

    Any ideas?

    Thanks !!
    Michael

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    The way that I tackled this was switching in and out of PrintPreview. Something like:<pre> With ActiveSheet
    ActiveWindow.View = xlPageBreakPreview
    With .PageSetup
    .PrintTitleRows = "$1:$1"
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .CenterHorizontally = True
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .CenterFooter = Date
    .RightFooter = "&P"
    End With
    .VPageBreaks(1).DragOff xlToRight, 1
    Set .HPageBreaks(1).Location = Range("A1").Offset(60, 0)
    Set .HPageBreaks(2).Location = Range("A1").Offset(120, 0)
    ActiveWindow.View = xlNormalView
    End With
    </pre>

    Rather than hard-coding the number of HPageBreaks, I would suggest that you loop through the UsedRange allowing for however many lines you require per page.

    HTH
    Gre

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    I'll give it a try.

    Guess I was just looking for the "easy" way !!

    Thanks !!
    Michael

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Add this within the with .. end with

    .Zoom = False

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Sorry, I misread your question. Try adding:

    <pre> .ZOOM=False
    </pre>

    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Thanks so much guys <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    Ya know, most of the other properties are 'intuitive'. Right Margin, Landscape, Orientation etc.
    How could I have discovered that ZOOM = Scaling/Fit to? Is it in the Help files somewhere?

    Thank you all so very much for sharing your knowledge. I know I say it all the time,
    but I really appreciate you guys.

    Michael

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    One way is by recording a macro.

    When you put a VALUE into the ADJUST TO, scaling % it puts something like:
    .zoom = 20

    If you select the FIT To in the dialog box, that is equivalent to NO ZOOM so:
    .zoom = false

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Page Format with Macro (WIN2000-XL97)

    Thanks Steve. I'm going to read up on Recording macros today !

    Michael

Posting Permissions

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