none
VBA Excel - Printing a UserForm RRS feed

  • Question

  • How can I print a UserForm ?

    1. When I write the code "me.printform" the Userform is printed but not in full. How can I change the size, font, landscape etc'.

    2. what is the code for clicking on the CommandButton so it will open a screen so I can choose the printer (like XPS or PDF) ?

    Tuesday, January 30, 2018 3:27 PM

Answers

  • Hi Ran_123,

    If your issue is solved then I suggest you to mark the helpful suggestion as an answer.

    It will help us to close this thread on our side.

    If you have any further questions then let us know about that.

    We will try to provide you further suggestions to solve it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ran_123 Friday, February 9, 2018 12:50 PM
    Friday, February 9, 2018 1:08 AM
    Moderator

All replies

  • Hi Ran_123,

    (1) When I write the code "me.printform" the Userform is printed but not in full. How can I change the size, font, landscape etc'.

    You can try to refer code below may help you.

    Option Explicit 
    
    Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
        ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    Const VK_SNAPSHOT = 44
    Const VK_LMENU = 164
    Const KEYEVENTF_KEYUP = 2
    Const KEYEVENTF_EXTENDEDKEY = 1
    
    Private Sub CommandButton1_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
        'added to force landscape
        ActiveSheet.PageSetup.Orientation = xlLandscape
        
       
    With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
    
        ActiveSheet.PageSetup.PrintArea = ""
        
        With ActiveSheet.PageSetup
            .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
            .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
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        ActiveWorkbook.Close False
    End Sub 
        
    

    Reference:

    Printing VBA userform as Landscape

    (2) what is the code for clicking on the CommandButton so it will open a screen so I can choose the printer (like XPS or PDF) ?

    You can try to show Dialog.

    Private Sub CommandButton1_Click()
    bResponse = Application.Dialogs(xlDialogPrinterSetup).Show
    If TypeName(response) = "Boolean" Then Exit Sub
    
    End Sub

    Output:

    Further, You can try to modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 31, 2018 2:18 AM
    Moderator
  • Hi,

    Thanks for your answers but I still have difficulties to perform.

    for (1) I get an error in the line "ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
            DisplayAsIcon:=False"

    for (2) it opens a window with the printers but when I choose a printer (or pdf printer) there is no response at all.

    Thanks again for your help!

    Sunday, February 4, 2018 1:49 PM
  • Hi Ran_123,

    For your first question, You need to share whole sample code that can reproduce the issue.

    With that one line, We are not able to reproduce the issue.

    For your second question, the code is just for displaying the dialog box.

    You need to code after that to set the printer.

    Looks like you just refer the second example.

    I suggest you to also refer the first one.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 5, 2018 8:28 AM
    Moderator
  • Hi,

    First question - It worked after I fixed my mistake, but the way it worked was by opening another workbook and print screen and copy to that workbook and to the printer.

    The problem is that my files contains many userforms that some of them need to be printed so it is not easy that way that new workbooks are opened.

    Is there a way to print without opening another workbook ? maybe to print it directly to a pdf file that I can choose if I want to save or print ?

    Second question - How do I combine the dialog box and the code ? do you have an example ?

    Thanks again for your help!

    Ran

    Wednesday, February 7, 2018 2:08 PM
  • Hi Ran_123,

    You had mentioned that,"but the way it worked was by opening another workbook and print screen and copy to that workbook and to the printer."

    I also see that you want to print some user forms and when you try it whole form is not getting print properly.

    For your overall issue, There is a one solution.

    You can try to create a Sheet in your workbook.

    When you want to print the user form then try to copy all the data to that sheet.

    Then you can easily print the worksheet to PDF with all the data.

    You can easily format the sheet and print it as you want.

    You can try to implement it may solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 8, 2018 9:23 AM
    Moderator
  • Thanks a lot !

    I will do just that.

    Thursday, February 8, 2018 5:49 PM
  • Hi Ran_123,

    If your issue is solved then I suggest you to mark the helpful suggestion as an answer.

    It will help us to close this thread on our side.

    If you have any further questions then let us know about that.

    We will try to provide you further suggestions to solve it.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ran_123 Friday, February 9, 2018 12:50 PM
    Friday, February 9, 2018 1:08 AM
    Moderator