none
Printing Userform RRS feed

  • Question

  • Hello

    I have created a userform that takes data from several different sheets in a document and displays charts depending on certain inputs. I want to be able to print the whole form, but

    A) I had to use a scroll bar to get everything I wanted onto the form and it only displays that visible portion when printing

    B) The form is too wide, so using

                  userform.Printform

        cuts off a significant portion.

    Is there a better way to make this happen? I considered trying to export it somehow and print the image, but I don't know how to make that work or if it's even possible. I only started learning VBA about two weeks ago and I have very limited coding experience even beyond that.

    Thanks for any help you're able to provide.


    • Edited by ScottMasteller Thursday, July 28, 2016 4:53 PM I mistyped some words
    Thursday, July 28, 2016 4:51 PM

Answers

  • From Tom Ogilvy, Excel MVP:

    In a general VBA module:

    'PtrSafe for 64 bit Windows

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

    Public Const VK_SNAPSHOT = 44
    Public Const VK_LMENU = 164
    Public Const KEYEVENTF_KEYUP = 2
    Public Const KEYEVENTF_EXTENDEDKEY = 1

    Sub Test()
        UserForm1.Show
    End Sub

    'In the userform's codemodule:
    Private Sub CommandButton1_Click()
        ' keybd_event VK_SNAPSHOT, 0, 0, 0
        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
            .Orientation = xlPortrait
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        ActiveWorkbook.Close False
    End Sub

    Thursday, July 28, 2016 6:12 PM

All replies

  • From Tom Ogilvy, Excel MVP:

    In a general VBA module:

    'PtrSafe for 64 bit Windows

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

    Public Const VK_SNAPSHOT = 44
    Public Const VK_LMENU = 164
    Public Const KEYEVENTF_KEYUP = 2
    Public Const KEYEVENTF_EXTENDEDKEY = 1

    Sub Test()
        UserForm1.Show
    End Sub

    'In the userform's codemodule:
    Private Sub CommandButton1_Click()
        ' keybd_event VK_SNAPSHOT, 0, 0, 0
        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
            .Orientation = xlPortrait
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
        ActiveWorkbook.Close False
    End Sub

    Thursday, July 28, 2016 6:12 PM
  • I'm sorry to bother you, but could you explain to me what this does exactly? As far as I can tell it puts the entire visible window of the userform into the clipboard, pastes it to a sheet, and then formats the printing from there. Is that in any way correct? And how does it copy the userform to the clipboard?

    Wednesday, August 3, 2016 2:07 PM
  • You are correct - the code takes a snapshot of the userform and pastes it into a workbook, which allow the print-to-fit option to be used (which is not available with userforms). How exactly it does it is - magic ;-)  Tom Ogilvy is a really smart guy - he uses the system library files to control the useform's window and what gets copied to the clipboard.
    Wednesday, August 3, 2016 2:15 PM
  • Great, Thanks a lot!
    Friday, August 5, 2016 1:49 PM