setup for userform printing

[Jacob]

I have created a spreadsheet that is run entirely from a userform. I
want the option to print out the userform. I currently use the
following code to perform this.

Private Declare Function OpenClipboard& Lib "user32" (ByVal hwnd&)
Private Declare Function EmptyClipboard& Lib "user32" ()
Private Declare Function CloseClipboard& Lib "user32" ()
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte _
, ByVal bScan As Byte, ByVal dwFlags&, ByVal dwExtraInfo&)

Private Sub PrintButton_Click()
Me.Repaint
OpenClipboard 0&
EmptyClipboard
keybd_event &H2C, 0, 0&, 0&
CloseClipboard
DoEvents
Application.ScreenUpdating = False
Dim NewBook As String
Workbooks.Add: ActiveSheet.Paste
NewBook = ActiveWorkbook.Name
With ActiveSheet.PageSetup
.RightFooter = Me.Caption & " Le &D Page &P/&N"
.PrintGridlines = False
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveWindow.Visible = False
Application.ScreenUpdating = True
Me.Hide
Windows(NewBook).SelectedSheets.PrintOut Copies:=1
Workbooks(NewBook).Close False
Me.Show
End Sub

this works well, with a couple exceptions. I would like to be able to
print the form without the background. and I would like to maximize
the form, so it prints as large as possible on the paper. Is this
possible and if so, how would I go about doing this? Thanks.

 

[Tom Ogilvy]

size the pasted picture to fit the pagebreaks.

What do you mean by background. You mean the grid of the spreadsheet

Activesheet.pagesetup.PrintGridlines = False

 

[Jacob]

I'm not sure how to size the picture.

I think it is simply using printscreen, taking a snapshot of what's on
the screen , and printing that. so I set it to minimize the excel
window (which I do not need at all) and leave only the user form. now
it is showing my desktop behind the form. I can see my icons etc. I
would like to see just my form being printed. thanks for your help.

 

[Tom Ogilvy]

this is the code I usually use:

Modification of code originally posted by
"Orlando Magalhães Filho" <[email protected]>

Modified to capture just the userform (not the whole window).

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 = &H2C

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 ' key down
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
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub

 

[Jacob]

Thanks Tom! Everything is working except it is printing an extra blank
page. here is my code adjusted for landscape etc.

Private Sub PrintButton_Click()
' keybd_event VK_SNAPSHOT, 0, 0, 0
DoEvents
keybd_event VK_LMENU, 0, _
KEYEVENTF_EXTENDEDKEY, 0 ' key down
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
With ActiveSheet.PageSetup
.RightFooter = Me.Caption & " Le &D Page &P/&N"
.PrintGridlines = False
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
ActiveSheet.Range("A1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub

 

[Jacob]

Nevermind. I changed .FitToPagesTall = False to .FitToPagesTall = 1,
and that fixed it.
The only other thing that would be nice is if I could control the
height and width. But it is fitting ok right now. slightly larger
margins than I would like but much better.

thanks for you help.

 

[Tom Ogilvy]

It is just a shape object on the worksheet. Shape objects have height,
width, top and left properties that you can set - although you should
maintain the aspect ratio.

--
Regards,
Tom Ogilvy


Nevermind. I changed .FitToPagesTall = False to .FitToPagesTall = 1,
and that fixed it.
The only other thing that would be nice is if I could control the
height and width. But it is fitting ok right now. slightly larger
margins than I would like but much better.

thanks for you help.