Excel Vba Print Userform and save Userform as pdf

2k Views Asked by At

I have a project that requires user entry form to be printed, renamed and saved to a specific folder

I've gone through numerous subjects relating to .printform command on this platform and usually the solutions requires to PrintScreen, save as bitmap, paste to new workbook and use Activesheet.ExportAsFixedFormat to set as pdf......

but then My userforms width is 603, My userforms height is 875, The userform requires a vertical scrollbar to view all details So "SnapScreen" wouldn't go a long way

The current code i have uses .printform command and asks me to choose the printer i want to print to and if the selected printer isn't active on my computer, it aborts command

Below is the code

Option Explicit
Private Const HWND_BROADCAST As Long = &HFFFF&
Private Const WM_WININICHANGE As Long = &H1A
 
Private Declare PtrSafe Sub keybd_event Lib "user32" _
(ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As LongPtr)

Private Declare PtrSafe Function SendNotifyMessage Lib "user32" Alias "SendNotifyMessageA" ( _
  ByVal hwnd As LongPtr, _
  ByVal Msg As Long, _
  ByVal wParam As Long, _
  lParam As Any) As Long
 
Private Declare PtrSafe Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" ( _
  ByVal pszPrinter As String) As Long
 
Public Sub ChangePrinter(NewPrinter As String)
 
  SetDefaultPrinter NewPrinter
  Call SendNotifyMessage(HWND_BROADCAST, _
    WM_WININICHANGE, _
    0, ByVal "windows")
 
End Sub
Private Sub btn_Print_Click()

Dim Msg As String
    Dim ireply As Integer


        ireply = MsgBox("Select Active-Printer", vbQuestion + vbOKCancel)
        Select Case ireply
            Case vbOK
                GoTo line123456
            Case vbCancel
                  Exit Sub
          End Select
          
line123456:
Dim dir as string
Dim OldPrinter As String
  Dim NewPrinter As String
  OldPrinter = Left(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)
  Application.Dialogs(xlDialogPrinterSetup).Show
  NewPrinter = Left(Application.ActivePrinter, InStrRev(Application.ActivePrinter, "on ") - 2)
''folderpath (created pdf to be renamed based on textbox value)
'dir =  "C:\Users\user\Documents\Almadina Related\NHIS RELATED" & Me.txt_pNameDefaultclaimsForm.Value & ".pdf"

ChangePrinter NewPrinter
  
  On Error GoTo Endproc

Me.PrintForm

GoTo SkipExit

Endproc:
MsgBox "Sorry, print command aborted - a default printer is not selected"
Unload Me
Exit Sub

SkipExit:

  ChangePrinter OldPrinter
  
End Sub

Kindly Help. is it possible to print, rename and save USERFORM to a specific folder as pdf

Thank You!

0

There are 0 best solutions below