Using VBA to print a user form to a PDF

[tfurnivall]

I recently needed to print a UserForm to a PDF file (in landscape orientation, naturally). After some effort I came up with the following code (almost none of which is totally original, but I've included it here in case it is of use to someone).

The first procedure is a procedure to capture the form, and save it as an image on a new worksheet in a new workbook:

1) Declare the screen-grab procedure (may need modification for a Win64 system).

'    The following declaration is necessary to get forms printing in Landscape mode

Private Declare PtrSafe Sub keybd_event Lib "User32" (ByVal bVk As Byte, _
    ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

'    These are the constants that we use to ' force'  Landscape mode
Const VK_SNAPSHOT = 44
Const VK_LMENU = 164
Const KEYEVENTF_KEYUP = 2
Const KEYEVENTF_EXTENDEDKEY = 1

2. The next procedure is the actual screen-grab procedure. This code has been around for some time, and I simply tidied it up a little. It effectively does an Alt-PrtScreen to copy a bitmap of the current window to the clipboard, then creates a new (and hence totally unuseed) workbook and worksheet, copies the clipboard into it, and makes it available to print.

Sub SDLGetPrintableFormImage(frm As UserForm)

'    Use fake keyboard events to take a snapshot of the form, and paste it into a
'    new worksheet. Then print the new worksheet.

'    It's ugly, but it works. Can be parameterized to your taste!

'    Allow events to be processed
DoEvents
 
Application.ScreenUpdating = False
 
'    Now force some events into the queue
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

'    Relinquish to other events - we have our snapshot
DoEvents

'    Create the new workbook, and copy the snapshot
Workbooks.Add
 
Application.Wait Now + TimeValue("00:00:01")
ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
DisplayAsIcon:=False
ActiveSheet.Range("A1").Select

'    Force landscape
ActiveSheet.PageSetup.Orientation = xlLandscape
 
'    Now configure the PageSetup for the new sheet
With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    .PrintArea = ""
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
 
' // One or more properties may not be available
'  .PrintQuality = 300
 
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

End Sub

3. The button handler for PrintForm button (I routinely 'disable' form event processing in a handler, to avoid problems). The 'missing' routine (SDLPrintForm) simply prints out the ActiveWorksheet to a real printer. I needed to be able to save PDFs of my form prints, to include in documentation (rather than having to recreate them when they were needed). There is an attachment of a PDF showing how I position my PrintForm button.

Private Sub cmdPrintForm_Click()

Const CONTROLNAME = "cmdPrintForm"
Const EVENTNAME = "Click"

Dim SaveProcessEvents As Boolean

Dim x As Long

SaveProcessEvents = Application.EnableEvents
If Not Application.EnableEvents Then
   Exit Sub
End If
Application.EnableEvents = False

'   Control/Event specific code goes here
If Me.chkPrintToPDF Then
   SDLPrintFormToPDF RepositoryForm, Me.Name & "--" & Format(Now(), "yyyy-mm-dd hh-mm-ss")
Else
   SDLPrintForm RepositoryForm
End If

'   End of control/specific code

Application.EnableEvents = SaveProcessEvents

End Sub

4) The code for the PrintForm as PDF procedure

Sub SDLPrintFormToPDF(frm As UserForm, FileName As String, Optional Path As String = "")

Dim WrkPath As String
Dim WrkFileName As String

'   Set  up the filename for the PDF
If Path = "" Then
   WrkPath = ActiveWorkbook.Path
Else
   WrkPath = Path
End If
WrkFileName = WrkPath & "\" & FileName & ".pdf"

'   Use the common routine to create a new workbook with a screen print of the form, in Landscape orientation
SDLGetPrintableFormImage frm


'   Add the name of the file in cell A31 of the new worksheet (this location found by trial and lots of error)
ActiveSheet.Range("A31") = WrkFileName

'   Now export the active sheet as a pdf

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                FileName:=WrkFileName
                                
'   Clean up the un-needed workbook
 ActiveWorkbook.Close False
 Application.ScreenUpdating = True
                                
End Sub

Attached is a PDF file (created with this software), to show the form that has the PrintForm button on it.

I've posted more than my share of weird requests in the past, and I hope that this may go some way to helping others who may be facing this particular problem!

Cheers,

Tony