Resizing and Printing a UserForm

K

kudruu

Hi,
I have a UserForm that I needed to print out on a 5'x8' sheet.
Currently I have (thanks to this forum)

Public Const VK_SNAPSHOT = 44
Public Const VK_LMENU = 164
Public Const KEYEVENTF_KEYUP = 2
Public Const KEYEVENTF_EXTENDEDKEY = 1
Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As
Long)
sub main()
'deleted code
UserForm1.show
End Sub

Private Sub print_form_Click()
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
Unload Me
End Sub

However, this is simply a screenshot and I can't seem to figure out
how to size it down (even with fooling with the numbers). Are there
any calculated or more exact resizing methods? Or maybe more
parameters I could include?

Thank you very much

-Andrew
 
D

Dave Peterson

5 feet by 8 feet?????????? <vbg>

Since you're only printing a worksheet, maybe you could just do the equivalent
of File|Page setup|Page Tab|Adjust to %.

Add a line like:

ActiveSheet.PageSetup.Zoom = 70
Before the .Printout command

In fact, you could experiment by commenting these lines:

' ActiveWindow.SelectedSheets.PrintOut Copies:=1
' ActiveWorkbook.Close False

Then when the code stops, you could resize it manually to make it fit the way
you want. Then modify the code to use that percentage.
 
K

kudruu

5 feet by 8 feet?????????? <vbg>

Since you're only printing a worksheet, maybe you could just do the equivalent
of File|Page setup|Page Tab|Adjust to %.

Add a line like:

ActiveSheet.PageSetup.Zoom = 70
Before the .Printout command

In fact, you could experiment by commenting these lines:

' ActiveWindow.SelectedSheets.PrintOut Copies:=1
' ActiveWorkbook.Close False

Then when the code stops, you could resize it manually to make it fit the way
you want. Then modify the code to use that percentage.

Sorry for my silly typo. It's 5 by 8 inches. Although a wall poster
of a bitmap image of my userform might be nice.
 
K

kudruu

5 feet by 8 feet?????????? <vbg>

Since you're only printing a worksheet, maybe you could just do the equivalent
of File|Page setup|Page Tab|Adjust to %.

Add a line like:

ActiveSheet.PageSetup.Zoom = 70
Before the .Printout command

In fact, you could experiment by commenting these lines:

' ActiveWindow.SelectedSheets.PrintOut Copies:=1
' ActiveWorkbook.Close False

Then when the code stops, you could resize it manually to make it fit the way
you want. Then modify the code to use that percentage.

Here's how I finished right before the print function (as an exact
size)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 360#
Selection.ShapeRange.width = 576#

That ends up being exactly 5"x8"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top