setup for userform printing
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
"Jacob" wrote in message
ups.com...
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.
Jacob wrote:
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.
|