Private Sub ajout_Click()
'*** bouton "ajout sur devis/facture"
Dim lig As Integer, i As Integer
Dim Sh As Worksheet, VPB As PageSetup
Dim LargeurCol As Single, MaHauteur As Single, Lg_Origine As Single
'calcul de la valeur de la variable lig
Dim Mot As String
Application.ScreenUpdating = False
Application.EnableEvents = False
If Me.TBqtevente.Value = "" Then
MsgBox "Entrer une quantité,svp"
Exit Sub
End If
With wsFacture
.Range("c18:M18,O18:P18").Borders(xlEdgeBottom).LineStyle = xlContinuous
' lig = .Range("B65536").End(xlUp)(2).Row
lig = .Range("B" & .Rows.Count).End(xlUp)(2).Row
If lig < 19 Then lig = 19
'insertion d'une ligne
'.Rows(lig + 1).Insert
.Range("C" & lig - 1 & ":P" & lig - 1).Copy
.Range("C" & lig).Insert xlShiftDown
.Range("C" & lig & ":P" & lig).ClearContents
.Range("C" & lig & ":H" & lig).HorizontalAlignment = xlLeft
If Not Me.TBarticles = "" Then
.Rows(lig) = ""
.Range("D" & lig) = TBarticles.Value
Lg_Origine = .Columns(3).ColumnWidth
LargeurCol = .Columns(3).ColumnWidth + .Columns(4).ColumnWidth + .Columns(5).ColumnWidth + .Columns(6).ColumnWidth + _
.Columns(7).ColumnWidth + .Columns(8).ColumnWidth
.Columns(4).ColumnWidth = LargeurCol
With .Range("D" & lig, "H" & lig)
.Font.Size = 14
.Font.Name = "arial"
.MergeCells = False
.WrapText = True 'retour du texte à la ligne
.EntireRow.AutoFit 'mettre la ligne en ajustement auto de la hauteur
MaHauteur = .RowHeight 'voir quelle est la hauteur de la ligne une fois cet autofit fait
.MergeCells = True 'refusionner
'.VerticalAlignment = xlCenter
.RowHeight = IIF(MaHauteur > 15, MaHauteur, 15) 'si la hauteur une fois autofit fait est inferieur à 15 je laisse 15 en minimum sinon hauteur de l'autofit (perso c'est la hauteur mini que je voulais
End With
End If
.Columns(4).ColumnWidth = Lg_Origine
'recopie et mise en forme des données dans la feuille facturation
.Cells(lig, "B") = Me.TBnum
.Cells(lig, "D") = Me.TBarticles
.Cells(lig, "D").Font.Bold = False
'.Cells(lig, "D").HorizontalAlignment = xlLeft
'.Cells(lig, "D").VerticalAlignment = xlCenter
.Range("D" & lig & ":H" & lig).Merge
.Cells(lig, "I") = Me.TBpu
.Cells(lig, "I").NumberFormat = "#,##0.00 €"
.Cells(lig, "J") = Me.TBunité
.Cells(lig, "K") = Me.TBqtevente
.Cells(lig, "M") = Abs(Me.OB20) + 1
'calcul du montant HT
If IsNumeric(.Cells(lig, "I")) And IsNumeric(.Cells(lig, "K")) Then
.Cells(lig, "O").FormulaR1C1 = "=IF(RC[-2]=1,RC[-6]*RC[-4]*0.10,"""")"
.Cells(lig, "O").NumberFormat = "#,##0.00 €"
.Cells(lig, "P").FormulaR1C1 = "=IF(RC[-3]=2,RC[-7]*RC[-5]*0.20,"""")"
.Cells(lig, "P").NumberFormat = "#,##0.00 €"
.Cells(lig, "L").FormulaR1C1 = "=RC[-1]*RC[-3]"
.Cells(lig, "L").NumberFormat = "#,##0.00 €"
End If
'calcul du montant HT
If IsNumeric(.Cells(lig, "I")) And IsNumeric(.Cells(lig, "K")) Then
'.Cells(lig, "L") = "=" & .Cells(lig, "I").AddressLocal & "*" & .Cells(lig, "K").AddressLocal
.Cells(lig, "L") = "=" & .Cells(lig, "I").Address(RowAbsolute:=False) & "*" & .Cells(lig, "K").Address(RowAbsolute:=False)
Else
.Cells(lig, "O") = ""
.Cells(lig, "P") = ""
End If
'calcul des totaux montant HT, TVA10, TVA 20
For i = lig To 1 Step -1
If .Cells(i, "K") = "REPORT" Or .Cells(i, "K") = "Quantité" Then Exit For
Next i
.Cells(lig + 1, "L").Formula = "=SUM(" & .Range(.Cells(i + 1, "L"), .Cells(lig, "L")).Address(RowAbsolute:=False) & ")"
.Cells(lig + 1, "L").NumberFormat = "#,##0.00 €"
.Cells(lig + 1, "O").Formula = "=SUM(" & .Range(.Cells(i + 1, "O"), .Cells(lig, "O")).Address(RowAbsolute:=False) & ")"
.Cells(lig + 1, "O").NumberFormat = "#,##0.00 €"
.Cells(lig + 1, "P").Formula = "=SUM(" & .Range(.Cells(i + 1, "P"), .Cells(lig, "P")).Address(RowAbsolute:=False) & ")"
.Cells(lig + 1, "P").NumberFormat = "#,##0.00 €"
If .Cells(lig + 1, "P") < 0.0001 Then .Cells(lig + 1, "P") = ""
If .Cells(lig + 1, "O") < 0.0001 Then .Cells(lig + 1, "O") = ""
'Remise a zéro du formulaire
TBnum.Value = ""
TBarticles.Value = ""
Me.TBtranche = ""
TBpu.Value = ""
TBqtevente.Value = ""
TBunité.Value = ""
'Formatage du tableau
.Cells(lig, "C").Borders(xlEdgeLeft).LineStyle = xlContinuous
.Range(.Cells(lig, "I"), .Cells(lig, "P")).Borders(xlEdgeLeft).LineStyle = xlContinuous
.Range(.Cells(lig, "C"), .Cells(lig, "M")).Borders(xlEdgeTop).LineStyle = xlNone
.Range(.Cells(lig, "O"), .Cells(lig, "P")).Borders(xlEdgeTop).LineStyle = xlNone
.Range(.Cells(lig, "C"), .Cells(lig, "M")).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(.Cells(lig, "O"), .Cells(lig, "P")).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(.Cells(lig, "D"), .Cells(lig, "H")).Borders(xlInsideVertical).LineStyle = xlNone
.Range(.Cells(lig, "I"), .Cells(lig, "Q")).Borders(xlInsideVertical).LineStyle = xlContinuous
.Range(.Cells(lig, "O"), .Cells(lig, "P")).VerticalAlignment = xlCenter
.Range(.Cells(lig, "I"), .Cells(lig, "M")).VerticalAlignment = xlCenter
With .Range("C19:M" & lig & ",O19:P" & lig)
.Font.Size = 14
.Font.Name = "arial"
End With
End With
wsFacture.Range("c19:M19").Borders(xlEdgeTop).LineStyle = xlContinuous
wsFacture.Range("O19:P19").Borders(xlEdgeTop).LineStyle = xlContinuous
ActiveWindow.ScrollRow = IIF((lig - NB_LIGNE_ARTICLE_FIGE) > Range("DOC_TITRE").Row, lig - NB_LIGNE_ARTICLE_FIGE, Range("DOC_TITRE").Row + 1)
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub