PDA

View Full Version : Solved: can I change some options when using PrintForm !!!!



joe55555
07-15-2013, 10:11 PM
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.

Aussiebear
07-15-2013, 11:16 PM
Try the following

Private Sub CommandButton1_Click()
Me.PrintForm
End Sub

joe55555
07-15-2013, 11:20 PM
i've tried to use this code and it didn't work.
i also used the following and it didn't work:

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

joe55555
07-15-2013, 11:22 PM
why the code is horizontally ?

Aussiebear
07-16-2013, 04:35 AM
Because of the method of applying the code to the forum. I have tried to edit your post to make it more easily read

SamT
07-16-2013, 09:50 AM
Print out this page frm MSDN:
Printing VBA userform as Landscape (http://social.msdn.microsoft.com/Forums/en-US/def468a2-a317-496e-85a1-ab37bd449264/printing-vba-userform-as-landscape)

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

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

joe55555
07-17-2013, 12:27 AM
thank you guys it worked fine :)