locked
VBA Excel - Printing a UserForm in 32-bit and 64-bit RRS feed

  • Question

  • Hi,

    The following code works great in one computer but isn't working on another - printing a userform.

    The first computer is 32-bit operating system and the other one is 62-bit.

    How do I change the code so it will fit both 32-bit and 62-bit ? (I suppose this is the explanation)

    The 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() 
        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 

    Wednesday, February 14, 2018 1:17 PM

All replies

  • Change the Private Declare part at the top to

    #If VBA7 Then
        Private Declare PtrSafe Sub keybd_event Lib "user32" _
            (ByVal bVk As Byte, ByVal bScan As Byte, _
            ByVal dwFlags As LongLong, ByVal dwExtraInfo As LongPtr)
    #Else
        Private Declare Sub keybd_event Lib "user32" _
            (ByVal bVk As Byte, ByVal bScan As Byte, _
            ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
    #End If

    It should then work in both 32-bit and 64-bit Office.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 14, 2018 3:50 PM
  • Hi,

    I tried it on a 32-bit computer and I got a compile error ("user-defined type not defined").

    So I erased the first paragraph and only wrote:

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

    And it worked. these 32-bit computer that I tried it on has a VBA 7 version.

    How should I change the code so it will work both on 32-bit and 64-bit ?

    Thanks,

    Ran

    Wednesday, February 14, 2018 4:45 PM
  • If you copy the code that I posted correctly, it should work in all versions.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 14, 2018 4:48 PM
  • I copy-paste the code as is and it didn't work.

    I switched between the two paragraph and then it worked.

    As I mentioned, I did it on a 32-bit computer which has VBA7.

    Maybe the "IF" code should relate to 32/64 BIT and not to the version of the VBA ?

    Wednesday, February 14, 2018 6:09 PM
  • On a PC that doesn't have VBA7, the first declaration won't be valid, but it will not be executed either, so it should work.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 14, 2018 7:13 PM
  • Hi Ran_123,

    Try to use the code below and let us know whether it works on your side or not.

    #If VBA7 Then
       Private Declare PtrSafe Sub keybd_event Lib "user32" _
       (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As LongPtr)
    #Else
        Declare Sub keybd_event Lib "user32" _
        (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As Long)
    #End If

    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 15, 2018 2:18 AM
  • Hi,

    I ran the new code and I got a run time error 1004, and the last 2 lines were marked:

    #If VBA7 Then
       Private Declare PtrSafe Sub keybd_event Lib "user32" _
       (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As LongPtr)
    #Else
        Declare Sub keybd_event Lib "user32" _
        (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwFlags As Long, _
        ByVal dwExtraInfo As Long)
    #End If
    Const VK_SNAPSHOT = 44
    Const VK_LMENU = 164
    Const KEYEVENTF_KEYUP = 2
    Const KEYEVENTF_EXTENDEDKEY = 1
    Private Sub CommandButton6_Click()
    Dim pdfName As String
    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
    ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
    DisplayAsIcon:=False

    Thursday, February 15, 2018 1:03 PM