me.printform

[David]

I am tying to set up a print button on a userform, and am
using the me.printform command. Unfortunately, the
userform would fit better as a landscape print job than
it would as a portrait. (portrait cuts off half of the
printer userform). How can I print a user form and set
some "page setup" parameters in the code for the
printform command. Thanks for anyones advice.

Dave

 

[Tom Ogilvy]

not supported. Easiest is to snap a picture of the userform, paste it in a
new workbook, do you formatting/setting of page setup parameters. print it,
close the workbook without saving.

Here is the basic code:

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
' make settings to page setup
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub