grisan29
XLDnaute Accro
bonjour a vous tous
j'essai de mettre un msgbox si une textbox n'est pas renseignée par
cela fonctionne mais le reste du code continu ses calculs et alors il y a un bug car le textbox9 n'est pas renseigné a
comment peut faire que le calcul s'arrête le temps de renseigné le textbox9, je vous joint l'ensemble du code
Pascal
j'essai de mettre un msgbox si une textbox n'est pas renseignée par
Code:
If Me.TextBox9.Value = "" Then MsgBox "Entrer une quantité, svp"
Code:
TextBox8 = Me.TextBox5.Text - Me.TextBox9.Text
Code:
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
Dim ctrMt, ctrTVA7, ctrTVA19 As Variant
Application.ScreenUpdating = False
Application.EnableEvents = False
If Me.TextBox9.Value = "" Then MsgBox "Entrer une quantité,svp"
With WsFacture
.Range("c18:M18,O18:P18").Borders(xlEdgeBottom).LineStyle = xlContinuous
lig = .Range("B65536").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.TextBox2 = "" Then
.Rows(lig) = ""
.Range("D" & lig) = TextBox2.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.TextBox1
.Cells(lig, "D") = Me.TextBox2
.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.TextBox3
.Cells(lig, "I").NumberFormat = "#,##0.00€"
.Cells(lig, "J") = Me.TextBox4
.Cells(lig, "K") = Me.TextBox9
.Cells(lig, "M") = Abs(Me.OptionButton2) + 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.07,"""")"
.Cells(lig, "O").NumberFormat = "#,##0.00€"
.Cells(lig, "P").FormulaR1C1 = "=IF(RC[-3]=2,RC[-7]*RC[-5]*0.196,"""")"
.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
Else
.Cells(lig, "O") = ""
.Cells(lig, "P") = ""
End If
'calcul des totaux montant HT, TVA5,5, TVA 19,6
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")).AddressLocal & ")"
.Cells(lig + 1, "L").NumberFormat = "#,##0.00€"
.Cells(lig + 1, "O").Formula = "=SUM(" & .Range(.Cells(i + 1, "O"), .Cells(lig, "O")).AddressLocal & ")"
.Cells(lig + 1, "O").NumberFormat = "#,##0.00€"
.Cells(lig + 1, "P").Formula = "=SUM(" & .Range(.Cells(i + 1, "P"), .Cells(lig, "P")).AddressLocal & ")"
.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
TextBox1.Value = ""
TextBox2.Value = ""
Me.TextBox7 = ""
TextBox3.Value = ""
TextBox9.Value = ""
TextBox5.Value = ""
TextBox8.Value = ""
TextBox4.Value = ""
'OptionButton3.Value = False
'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)
TextBox8 = Me.TextBox5.Text - Me.TextBox9.Text
If TextBox8 < 0 Then TextBox8 = 0
TextBox5 = TextBox8
Application.ScreenUpdating = True
Application.EnableEvents = True
End SubPascal