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

 

[excelsishya]

Yes that is there.So is there any other way out of this problem?

 

[Peter_SSs]

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.

Thanks Jason. Certainly some varied & unpredictable results. I'm not sure of a solution at this stage.

 

[jasonb75]

A rather crude 'fix' but it appears to work, although I haven't tested thoroughly.

Sub test()
Dim btest As Boolean
Line1:
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
    btest = False
    Else
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    btest = (btest = False)
    End If
 
End With
If btest Then GoTo Line1
End Sub

Any thoughts Peter?

 

[TkdKidSnake]

VBA To Add Page Footer Not Working Correctly

My apologies didn't mean to post on here