Print Macro

[grahammal]

The following code is under my Print Macro button and works OK except that
I have increased the size of the form that it prints out and now spills onto a second page by about 5 lines.
What can I include into the below code to fit the printout onto 1 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 Sub

[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


--
Don Guillett
SalesAid Software
[email protected]
"grahammal" <[email protected]> wrote
in message news:[email protected]...
>
> The following code is under my Print Macro button and works OK except
> that
> I have increased the size of the form that it prints out and now spills
> onto a second page by about 5 lines.
> What can I include into the below code to fit the printout onto 1
> 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 Sub
>
>
> --
> grahammal
> ------------------------------------------------------------------------
> grahammal's Profile:
> http://www.excelforum.com/member.php...o&userid=20336
> View this thread: http://www.excelforum.com/showthread...hreadid=538868
>

[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

--
Regards,
Tom Ogilvy


"grahammal" wrote:

>
> Have just tried the above but it still overflows to page 2 by the same
> amount?
>
>
> --
> grahammal
> ------------------------------------------------------------------------
> grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
> View this thread: http://www.excelforum.com/showthread...hreadid=538868
>
>

[grahammal]

Have now got it to work just fine.
Thanx