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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
| 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
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 = ""
'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 Sub |
Afficher msgbox si textbox vide
Répondre avec citation
Partager