1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Utilis & Chr(10) &
Dim VPB As VPageBreak, HPB As HPageBreak
Dim No As String, Dt As String
Application.ScreenUpdating = False
'No = Year(Now) & " - " & Sheets("facturation").Range("A1")
Dt = Format(Date, "dd mmmm yyyy")
'formatter en vue d'impression
With ActiveSheet
'reset parametres d'impression
.ResetAllPageBreaks
With .PageSetup
'redefinition de la plage d'impression
.PrintArea = "$C$1:$M$" & [MTTC].Row + 13
.CenterHeader = ""
.CenterFooter = ""
End With
'passage en break preview sinon erreur avec DragOff
ActiveWindow.View = xlPageBreakPreview
'déplacement des sauts de page
For Each VPB In .VPageBreaks
VPB.DragOff Direction:=xlToRight, RegionIndex:=1
Next VPB
'retour à vue normale
ActiveWindow.View = xlNormalView
'ajout de ligne avant et après chaque saut de page pour délimiter le cadre lors de l'impression
For Each HPB In .HPageBreaks
.Range(.Cells(HPB.Location.Row - 1, 3), .Cells(HPB.Location.Row - 1, 12)).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(.Cells(HPB.Location.Row, 3), .Cells(HPB.Location.Row, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous
Next HPB
'entête au centre
'.PageSetup.CenterHeader = "&14&""Arial,Gras""FACTURE SAV N° " & No & " en date du " & Dt
'pied de page au centre
'.PageSetup.CenterFooter = "&14&""SIRET : 0123456789 - NAF : 0000 - RCS : 00000 - N° TVA : FR001234578969" & Chr(10) & _
'"assurance décennale n°000000 de chez untel"
End With
Application.ScreenUpdating = True
End Sub |
Partager