none
Printing VBA userform as Landscape

    Question

  • Hi
    I have tried printing a VBA userform as Landscape, but I can't find out how this is done.
    I have tried with
             Userform1.PageSetup.Orientation = xlLandscape
    but it does not work.
    If I use
             Userform1.PrintForm
    it will be printed as Portrait and only some parts of the form is being printed.
    I have asked other VBA forums for an answer but still have no answer.
    Hope someone have an answer.
    Thanks in advance.
    Wednesday, May 13, 2009 11:10 AM

Answers

  • Dear Friend,

    Paste below code in you user form. I assume that your user form is having CommandButton1 , you can change it as per your requirement.

    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
       


       
    Please let me know, about your query and view. Thanks.

    Rgrds, Brij http://accessvbadeveloper.wordpress.com
    • Proposed as answer by WPF_Rock Sunday, May 24, 2009 11:31 AM
    • Edited by WPF_Rock Sunday, May 24, 2009 11:48 AM Removing unused variable
    • Marked as answer by VBAnders Wednesday, June 03, 2009 1:24 PM
    Sunday, May 24, 2009 10:44 AM

All replies

  • You could give this a shot, it involves taking an image of the userform, pasting it to a new, temporary worksheet whose orientation is set to landscape...



    Print Userform Landscape - VBA



    apparently, pagesetup cannot be set for a userform
    "The new phonebooks are here!"
    Thursday, May 14, 2009 7:41 PM
  • Userform does not have property of page setup.
    You need to understand that you are printing form not work sheet.

    I have question for you , why you are printing userForm.

    Apply your data on excel sheet and do proper page setup using vba code, and get print, that is the best option.

    Best wishes. Thanks.
    Rgrds, Brijraj Rathod http://accessvbadeveloper.wordpress.com
    • Proposed as answer by WPF_Rock Friday, May 15, 2009 10:48 AM
    Friday, May 15, 2009 10:48 AM
  • Hi VBA-Developer

    I have made an interactive (userform-) model which is intended for use for non-programmers.
    When they have made some analysis within the model they want a print of what they have
    made - still being within the framework of the userform model.
    So I need the printing function - in landscape!!! - for my userform.

    Hope for some idea.

    Thanks.
    Tuesday, May 19, 2009 9:13 AM
  • Dear Friend,

    Paste below code in you user form. I assume that your user form is having CommandButton1 , you can change it as per your requirement.

    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
       


       
    Please let me know, about your query and view. Thanks.

    Rgrds, Brij http://accessvbadeveloper.wordpress.com
    • Proposed as answer by WPF_Rock Sunday, May 24, 2009 11:31 AM
    • Edited by WPF_Rock Sunday, May 24, 2009 11:48 AM Removing unused variable
    • Marked as answer by VBAnders Wednesday, June 03, 2009 1:24 PM
    Sunday, May 24, 2009 10:44 AM
  • Hi VBA-Developer

    It works!
    Thanks a lot!
    You are no. 1!!!

    Wednesday, June 03, 2009 1:28 PM
  • Dear Friend,

    Paste below code in you user form. I assume that your user form is having CommandButton1 , you can change it as per your requirement.

    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

    I am trying to do the same thing and I woundered where do you put the:
    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

    part of the code. When I paste this into my userform it puts that bit under the End Sub of a previous button and says " Comliler Error: Only Comments may appear after End Sub..."
    Thursday, November 19, 2009 12:18 PM
  • Hi,

     

    It needs to be at the top of the module.

     

    Cathrine

    Friday, November 20, 2009 5:00 PM
  • This works great.  Thank you.
    Wednesday, July 11, 2012 9:00 PM
  • Great idea from WPF_Rock.

    I am using Win 7 64Bit and Office 2010 32Bit.

    I had a problem when the userform was larger than the paper. It should have fitted with FirToPagesWide and ..Tall but it just was not working. I modified the code to set the print area and it now works so thought I would post the modified code.

    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()
        Dim strPrintArea
         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
        
        With ActiveSheet.PageSetup
                 .PrintTitleRows = ""
                 .PrintTitleColumns = ""
        End With
     
        ActiveSheet.Shapes(1).Select    'Will always be Shape 1
       
        With Selection  'Get print area of picture
            strPrintArea = .TopLeftCell.Address & ":" & .BottomRightCell.Address
        End With
       
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
       
        With ActiveSheet.PageSetup
            .PrintArea = strPrintArea  'Set Print Area
            .PrintGridlines = False
            .CenterHorizontally = True
            .CenterVertically = True
            .Orientation = xlLandscape
            .PaperSize = xlPaperA4
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
        End With
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        
        ActiveWorkbook.Close False
     End Sub


    Regards, OssieMac

    Thursday, July 12, 2012 4:00 AM
  • How would I have to change this for 64-bit?  I have a UserForm that I want to do this too but when I use this code it tells me I have to update the declarations.  Been looking all over for a 64-bit version of this process without success. 

    anyone can help I would really appreciate it!! :)

    Thanks!

    Lee

    Sunday, July 15, 2012 7:03 PM
  • The declaration for keybd_event should be changed to

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

    The rest can remain the same, as far as I can tell.

    Regards, Hans Vogelaar

    Sunday, July 15, 2012 7:25 PM
  • thank you for this .

    It was exactly what I was looking for to be able to print a huge userform with lot's of information pulled from the active worksheet.

    Much appreciated.

    Lewis

    Thursday, April 10, 2014 10:50 AM