Consulting

Results 1 to 7 of 7

Thread: Solved: can I change some options when using PrintForm !!!!

  1. #1
    VBAX Newbie
    Joined
    Jul 2013
    Posts
    5
    Location

    Solved: can I change some options when using PrintForm !!!!

    Dear all,

    i'm writing a small software for my company using excel vba.
    i accomplished everything but i'm facing a problem with the UserForm.PrintForm.

    I created a button and wrote inside it the simple "UserForm1.PrintForm"

    1: i can't print my form on paper, all i get is a save as PDF.
    2: when i save it as PDF, my form is positioned on the top left on an A4 paper

    what i want to do is:

    1: is it possible to print directly my form on a paper or it should be saved as a PDF then i go print it from PDF?
    2: can i center my form to be in the middle of the PDF A4 paper?
    3: is it possible to hide the buttons of commands i added to my UserForm1 like "exit, print" button so when print it, i only have the complete form without the buttons and after this the buttons show back?

    thank you

    P.S. i tried the worksheet print it worked very good, but the boss is insisting on the form, he doesn't want the sheet.

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Try the following
    [VBA]
    Private Sub CommandButton1_Click()
    Me.PrintForm
    End Sub[/VBA]
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Newbie
    Joined
    Jul 2013
    Posts
    5
    Location
    i've tried to use this code and it didn't work.
    i also used the following and it didn't work:
    [VBA]
    VB:
    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 PrintButton_Click()
    LoadButton.Visible = False
    SaveAsButton.Visible = False
    SaveButton.Visible = False
    PrintButton.Visible = False
    ClearButton.Visible = False
    PrintPDFButton.Visible = False
    ExitButton.Visible = False
    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
    LoadButton.Visible = True
    SaveAsButton.Visible = True
    SaveButton.Visible = True
    PrintButton.Visible = True
    ClearButton.Visible = True
    PrintPDFButton.Visible = True
    ExitButton.Visible = True
    End Sub[/VBA]

  4. #4
    VBAX Newbie
    Joined
    Jul 2013
    Posts
    5
    Location

    i tried this one also

    why the code is horizontally ?

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Because of the method of applying the code to the forum. I have tried to edit your post to make it more easily read
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Print out this page frm MSDN:
    Printing VBA userform as Landscape

    Show it to your boss and tell him that he's an ***

    [VBA]Option Explicit

    Dim NoPrintControls As Collection

    Private Sub UserForm_Initialize()
    LoadNoPrintControls
    'Other initializing subs and functions here
    End Sub

    Private Sub PrintButton_Click()
    'First must set windows default printer to selected printer.
    'PrintForm 'Must manually set selected printer to Protrait, etc
    PrepareToPrint (True)
    HideNoPrintControls (True)
    MakeSheetForPictureOfForm (True) 'FormPictureSheet
    PrintMe
    MakeSheetForPictureOfForm (False) 'Delete Sheet
    HideNoPrintControls (False)
    PrepareToPrint (False)
    End Sub

    Function PrepareToPrint(YesNo As Boolean)
    'True to prepare, false to resore defaults
    If YesNo Then
    'Code to store all windows printer defaults
    Else
    'Code to Restore printer defaultts
    End If
    End Function

    Function LoadNoPrintControls()
    With NoPrintControls
    .Add CommandButton1
    .Add Label2
    .Add TextBox3
    'Etc
    End With
    End Function

    Function HideNoPrintControls(YesNo As Boolean)
    'YesNo = True to hide, False to show
    Dim Ctrl As Object
    For Each Ctrl In NoPrintControls
    Ctrl.Visible = Not (YesNo) 'Invert YesNo
    Next Ctrl
    End Function

    Function PrintMe()
    'See:
    'Printing VBA userform as Landscape, From MSDN:
    'Link:
    'http://social.msdn.microsoft.com/Forums/en-US/def468a2-a317-496e-85a1-ab37bd449264/printing-vba-userform-as-landscape
    '
    'Put that code in a standard module, Ex: "Module1."
    'Call it from this function. Example follows:
    PrintPicture Me, FormPictureSheet 'Where PrintPicture is the name of the sub with the MSDN code
    End Function
    [/VBA]
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Newbie
    Joined
    Jul 2013
    Posts
    5
    Location
    thank you guys it worked fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •