Search code examples
excelvbaprintinguserform

Excel VBA UserForm printscreen save as pdf


I have UserForm where I have button to take print screen of UserForm, paste to newly created worksheet and export it as pdf. Problems I have with current code are:

1) I want to fit print screen to one page, now it takes print screen and splits to two pages.

2) Higher resolution of print screen. Is it possible to increase resolution of print screen for more higher quality?

Code I use for button on UserForm:

Private Sub btnPrintPDF_Click()
'change to your button name
    Dim pdfName As String
    Dim newWS As Worksheet

    Application.DisplayAlerts = False

    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 'Otherwise, all of screen would be pasted as if PrtScn rather than Alt+PrtScn was used for the copy.

    Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
    pdfName = ThisWorkbook.Path & "\" & Sheets("MAIN").Range("D14").Value & "Project_Summary" & "_" & " " & Format(Now, "yyyy-mmm-dd") & ".pdf"
    newWS.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    newWS.Delete
    Unload Me
    Application.DisplayAlerts = True
    ThisWorkbook.Sheets("MAIN").Activate
End Sub

Solution

  • Application.PrintCommunication = False
    With newWS.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
    End With
    Application.PrintCommunication = True
    ewWS.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False