setup for userform printing
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
Tom Ogilvy wrote:
this is the code I usually use:
Modification of code originally posted by
"Orlando Magalhães Filho"
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
--
Regards,
Tom Ogilvy
"Jacob" wrote:
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 wrote:
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
--
Regards,
Tom Ogilvy
"Jacob" wrote:
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.
|