VBA To Set selected print area Fit to 1 page and then check For zoom/visibility

[excelsishya]

Hi all,

Does anyone have VBA code which will set selected print area to Land scape, fit to 1 page then will check for zoom if it is very small below 30, it will adjust it 50 and making row 3 and column B to repeat.

I am able to get most of things in recorded macro but checking for zoom after fitting print area to 1 page and then changing it to adjust to 50 if zoom is not 30 i am unable to get.

Excel 2007

Thanks

 

[VoG]

Zoom has to be set to False for fit to 1 page to work. Try

Sub PrintSet()
With ActiveSheet.PageSetup
    .PrintTitleRows = "$3:$3"
    .PrintTitleColumns = "$B:$B"
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
End Sub

 

[jasonb75]

To set the zoom level

With ActiveSheet.PageSetup
    .PrintTitleRows = "$3:$3"
    .PrintTitleColumns = "$B:$B"
    .Zoom = False
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = 1
If .Zoom < 30 Then .Zoom = 50
End With

I've been trying to make a dynamic version to "fit to page(s)", this seems to be somewhere close but I haven't tested thoroughly.

Dim vPage As Long, hPage As Long
With ActiveSheet.PageSetup
    .PrintTitleRows = "$3:$3"
    .PrintTitleColumns = "$B:$B"
    .Zoom = 100
    .Orientation = xlLandscape
    vPage = ActiveSheet.VPageBreaks.Count + 1
    hPage = ActiveSheet.HPageBreaks.Count + 1
    .Zoom = False
    .FitToPagesWide = Application.WorksheetFunction.RoundUp(vPage * 0.3, 0)
    .FitToPagesTall = Application.WorksheetFunction.RoundUp(hPage * 0.3, 0)
End With

 

[excelsishya]

Thank you VoG and Jasonb75 for providing Code.

Jason your 1st code itself will meet my requirement.

 

[Peter_SSs]

Jason your 1st code itself will meet my requirement.

Are you sure? Test it on a sheet with only cell A1 with data. Doesn't it still set the Zoom to 50 when clearly it could be 100? The reason being, as VoG has suggested that fitting to 1 page forces Zoom to be False which will always evaluate to < 30.

Borrowing from Jason's code and this page, try this:

With ActiveSheet.PageSetup
    .PrintTitleRows = "$3:$3"
    .PrintTitleColumns = "$B:$B"
    .Zoom = False
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    
    ExecuteExcel4Macro ("PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})")
    If .Zoom < 30 Then
        .Zoom = 50
    Else
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End If
    
End With

 

[excelsishya]

Hi Peter_SSs

Thanks for checking that possibility.Ya as you said when less data is there zoom was still 50 when it could have been 100.

When i checked Your modified code on data set

row3 has months running across( b3 to l3)
accounts description from col b (b4 to b575) down
total data range b3:l575
zoom was 10 fitting to 1 page when it should have been 50

Could you please test it on above kind of data set?

Thanks for the help.

 

[Peter_SSs]

Hi Peter_SSs

Thanks for checking that possibility.Ya as you said when less data is there zoom was still 50 when it could have been 100.

When i checked Your modified code on data set

row3 has months running across( b3 to l3)
accounts description from col b (b4 to b575) down
total data range b3:l575
zoom was 10 fitting to 1 page when it should have been 50
Could you please test it on above kind of data set?

Thanks for the help.

I set up a sheet as you described with Zoom at 100. Print Preview indicated 14 pages for me (I already had selected Landscape).

I manually change to Fit to 1 page wide by 1 page tall. Print Preview showed just 1 (squashed) page as you would expect. Zoom showed 10.

I manually changed back to "Adjust to 100%" and then ran the code. Print Preview indicated 7 pages. Zoom showed 50%.

Isn't that what you want if fitting to one page only makes the Zoom less than 30?

I'm not sure what you mean by the red bit above. You can't have it set to 1 page and a Zoom of 50. For that data it will need to be one or the other.

Also, when you say "fit to 1 page" do you mean ..

a) Fit to 1 page wide by 1 page tall, or

b) Fit to 1 page wide (by however many pages needed tall), or

c) (However many pages needed wide by) 1 page tall?

 

[jasonb75]

Peter, I think you're having problems similar to those I had with the alternative code in my earlier reply.

Try the test again with the same range, then run the code twice and view the preview.

 

[excelsishya]

Jason

You are correct only when we run code twice it messes up, for 1 time it works perfectly well.

Could we prevent code from running again on same page if it already run on page one time to prevent this error from occurring?

Thanks Jason & Peter for Help.

 

[jasonb75]

It's possible, but may not be the ideal solution.

What if the sheet is changed and you need to re-run the code and reprint the sheet?