print userform and setting margin

[Mark]

Hi,
I'd like print userform and setting (enlarge) leftmargin.
Actually my printout is shift max to left and top.
Is there any solution in VBA (in module or class)?
i have code in body userform now.
Me.PrintForm
I've excel 2k.

Any help will be apreciate.

Regards
Mark

 

[Tom Ogilvy]

PrintForm doesn't offer any options.

You can snap a picture of the form, then put it on the worksheet and print
it how you wish.

This was posted by
"Orlando Magalhães Filho" <[email protected]>

A while back and seems to work:

In a general 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 = &H2C

Sub Test()
UserForm1.Show
End Sub


In the userform module:


Private Sub CommandButton1_Click()
keybd_event VK_SNAPSHOT, 0, 0, 0
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
==============
Adjust it to suit your needs.

 

[Mark]

Hi Tom!
I had to modified code:
ActiveSheet.PasteSpecial ,,Link:=False,DisplayAsIcon:=False
and Your snapshot work fine, but i want print userform
only.
I've file with only operate on userform. Code:
Me.PrintForm work almost good (print only userform not all
window).
Still lack me setting in enlarge left margin and print
only userform automatically.
Tom, is it possible to make?

Best Regards
Mark with excel2k

 

[Tom Ogilvy]

In my experience, the code I posted only pastes a copy of the userform.

If you are getting more in your printout, it means you have not adjusted the
printarea of the worksheet where you pasted the image of the userform to
only print the userform (obviously turn off the grid on that page as well).
In additiona, if you want to adjust the left margin, you would simulate that
by adjusting the printarea of the sheet and the location of the image of the
userform within that printarea.

--
Regards,
Tom Ogilvy

Hi Tom!
I had to modified code:
ActiveSheet.PasteSpecial ,,Link:=False,DisplayAsIcon:=False
and Your snapshot work fine, but i want print userform
only.
I've file with only operate on userform. Code:
Me.PrintForm work almost good (print only userform not all
window).
Still lack me setting in enlarge left margin and print
only userform automatically.
Tom, is it possible to make?

Best Regards
Mark with excel2k

 

[Mark]

Hi Tom!
I don't know your code (api). I know a bit of VBA.
Actually i have pasted whole window (application and
userform), and i'd like only userform. It isn't dependent
on printarea - image paste in worksheet by your code is
snapshot all display in my monitor.
Tom, thank you for your reply.
Can you resolve my problem?

I work in excel 2k and WIN 2000.

Best Regards
Mark

 

[Tom Ogilvy]

You are correct and I was wrong. Here is a version that will capture just
the userform:

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

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

In a general 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
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False
End Sub

--
Regards,
Tom Ogilvy
Hi Tom!
I don't know your code (api). I know a bit of VBA.
Actually i have pasted whole window (application and
userform), and i'd like only userform. It isn't dependent
on printarea - image paste in worksheet by your code is
snapshot all display in my monitor.
Tom, thank you for your reply.
Can you resolve my problem?

I work in excel 2k and WIN 2000.

Best Regards
Mark