Print Macro

[grahammal]

The following code is under my Print Macro button and works OK excep
that
I have increased the size of the form that it prints out and now spill
onto a second page by about 5 lines.
What can I include into the below code to fit the printout onto
page??
Want it to work like the 'File' - 'Page Setup' - 'Fit To' option.

Sub Button4_Click()
'Print Forms
YesNo = MsgBox("Your selected Form will now be printed.", vbYesNo
vbCritical, " Print Form")
Select Case YesNo
Case vbYes
If vbYes Then GoTo 20 Else GoTo 10
10 Case vbNo
If vbNo Then GoTo 30
End Select
20 Application.ScreenUpdating = False
If ActiveSheet.Name = "Moves Request Form" Then
Else
End If
Sheet1.PageSetup.PrintArea = "B1:CW43" 'Sets the Print area
ActiveSheet.PrintOut 'Prints the Print area
Sheet1.PageSetup.PrintArea = "" 'Clears the Print area
If ActiveSheet.Name = "Moves Request Form" Then
Else
End If
Application.ScreenUpdating = True
30 End Su

 

[Don Guillett]

does this help
with Sheet1.PageSetup
.PrintArea = "B1:CW43" 'Sets the Print area
..FitToPagesWide = 1
..FitToPagesTall = 1
sheet1.PrintOut 'Prints the Print area
.PrintArea = "" 'Clears the
end with

 

[Ivan Raiminius]

Hi Grahammal,

a bit strange using of select case, also I didn't understand the
purpose of "If ActiveSheet.Name = "Moves Request Form" Then", so I
deleted it and rearanged your code:

Sub Button4_Click()
dim yesno as variant
'Print Forms
YesNo = MsgBox("Your selected Form will now be printed.", vbYesNo +
vbCritical, " Print Form")
Select Case YesNo
Case vbYes
Application.ScreenUpdating = False
with Sheet1.PageSetup
..PrintArea = "B1:CW43" 'Sets the Print area
..FitToPagesWide = 1
..FitToPagesTall = 1
ActiveSheet.PrintOut 'Prints the Print area
..PrintArea = "" 'Clears the Print area
end with
Application.ScreenUpdating = true
Case else
exit sub
End Select
End Sub

Regards,
Ivan

 

[grahammal]

Have just tried the above but it still overflows to page 2 by the same
amount?

 

[Tom Ogilvy]

with Sheet1.PageSetup
.PrintArea = "B1:CW43" 'Sets the Print area
.FitToPagesWide = 1
.FitToPagesTall = 1
.Zoom = False
sheet1.PrintOut 'Prints the Print area
.PrintArea = "" 'Clears the
end with

 

[grahammal]

Have now got it to work just fine.
Than