using printform command in excel

[Solar]

I am using the printform command as part of a User interface. The form as designed does not fit in the portrait print orientation. IS there a command that can be used in conjunction with the print form command to make the form fit the page? Or a way to resize it? The ultimate goal here is to get the form to print on one page, in the portrait orientation.

Thanks for the help.

 

[Andrew Poulsom]

No. The PrintForm method sends a bit-by-bit image of a UserForm object to the printer. There are no arguments for scaling.

 

[TommyGun]

Okay, using some code that was found here to control API calls, this method should work for you. Paste the following code into your UserForm. It works on the press of a button.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Sub</SPAN> keybd_event <SPAN style="color:#00007F">Lib</SPAN> "user32" (<SPAN style="color:#00007F">ByVal</SPAN> bVk <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, ByVal _
  bScan <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> dwFlags <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> dwExtraInfo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>)

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> KEYEVENTF_KEYUP = &H2
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> VK_SNAPSHOT = &H2C
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Const</SPAN> VK_MENU = &H12

<SPAN style="color:#007F00">'==========================================================================</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()

<SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> sh <SPAN style="color:#00007F">As</SPAN> Worksheet

CommandButton1.SetFocus
AltPrintScreen
DoEvents

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Add(template:=xlWBATWorksheet)
<SPAN style="color:#00007F">Set</SPAN> sh = wb.Sheets(1)

sh.Paste

<SPAN style="color:#00007F">With</SPAN> sh.PageSetup
    .FitToPagesTall = 1
    .FitToPagesWide = 1
    .Orientation = xlPortrait
    .Zoom = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

sh.PrintOut

wb.<SPAN style="color:#00007F">Close</SPAN> <SPAN style="color:#00007F">False</SPAN>

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">Set</SPAN> sh = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> wb = <SPAN style="color:#00007F">Nothing</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'==========================================================================</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> AltPrintScreen()
    keybd_event VK_MENU, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Andrew Poulsom]

Nice research Tommy!

As usual I should have included a caveat when I said no.

 

[TommyGun]

Thanks Andrew. I tried using SendKeys "%{PRTSC}" but it didn't work. I'm assuming cause it was sending the key inputs to XL and not the OS environment. But anyway, the code is cool to know.

 

[Solar]

Thanks for the reply!

Worked well