Print a Userform To Fit On One Page

[RyanH]

I would like to print a userform with a commandbutton, but the entire
userform does not fit on the page. Is there a way to click the control
button that is located on the userform and print the form to fit on one page?

This is what I have:

Private Sub btnPrint_Click()

frmItemSummary.PrintForm
MsgBox "Item # " & Cells(ActiveCell.Row, "A") & " has been sent to the
Printer.", vbInformation

End Sub

 

[Tom Hutchins]

I tweaked some code by Tom Ogilvy code. It copies the userform to a
worksheet and then prints it from there.

'In a general VBA module:
Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Public Const VK_SNAPSHOT = 44
Public Const VK_LMENU = 164
Public Const KEYEVENTF_KEYUP = 2
Public Const KEYEVENTF_EXTENDEDKEY = 1

Sub Test()
UserForm1.Show
End Sub

'In the userform module:
Private Sub CommandButton1_Click()
' keybd_event VK_SNAPSHOT, 0, 0, 0
DoEvents
keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
KEYEVENTF_KEYUP, 0
keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
KEYEVENTF_KEYUP, 0
DoEvents
Workbooks.Add
Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
DisplayAsIcon:=False
ActiveSheet.Range("A1").Select
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub

If desired, change the .Orientation value to xlLandscape.
Hope this helps,

Hutch

 

[JLGWhiz]

Maybe resize the userform before printing to shrink it to within the page
breaks?

 

[RyanH]

I think I have seen this code from Tom Ogilvy before. It works great. Do
you know how to explain most of the lines of code here? I would like to
understand what is happening here instead of just copying it.