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
|
Private Sub UserForm_Initialize()
Dim LastLig As Long
Dim i As Byte
Dim u As Integer
With Worksheets("Source")
LastLig = .Cells(.Rows.Count, 15).End(xlUp).Row
For i = 1 To 14 '10 nombre de tes commbo cmb_ref01...cmb_ref21
Me.Controls("cmb_nom" & i).RowSource = .Name & "!" & .Range("n2:n" & LastLig).Address
Me.Controls("cmb_type" & Format(i, "00")).RowSource = .Name & "!" & .Range("e2:e" & LastLig).Address
Me.Controls("cmb_produit" & Format(i, "00")).RowSource = .Name & "!" & .Range("l2:l" & LastLig).Address
Me.Controls("cmb_marque" & Format(i, "00")).RowSource = .Name & "!" & .Range("k2:k" & LastLig).Address
Next i
End With
For u = 1 To 14
With Me.Controls("commande" & u)
Me.Controls("commande" & u).Value = Sheets("n°commande").Range("e65536").End(xlUp).Offset(u, -1).Value
End With
Next u
End Sub
Private Sub cmd_validez_Click()
Dim cmb_nom01, cmb_nom02, cmb_nom03, cmb_nom04, cmb_nom05, cmb_nom06, cmb_nom07, cmb_nom08, cmb_nom09, cmb_nom10, _
cmb_nom11, cmb_nom12, cmb_nom13, cmb_nom14 As String
Dim Commande As Excel.Worksheet
Set Commande = Application.ThisWorkbook.Worksheets("n°commande")
Dim u As Integer
On Error Resume Next
'inscrire les noms en feuille n°de commande
For u = 1 To 14
With Me.Controls("cmb_nom" & u)
If Me.Controls("cmb_nom" & u).Value <> "" Then Commande.Range("e65536").End(xlUp).Offset(1, 0) = Me.Controls("cmb_nom" & u).Value
End With
Next u
Dim i As Byte, Indice As Byte, derlig As Integer
Dim pv As Variant
Dim numdl As Integer
pv = Controls("txt_pv" & Format(i, "00"))
With Worksheets("prepafact")
derlig = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To 14
If Controls("cmb_nom" & i) <> "" Then
Indice = Indice + 1
.Range("A" & Indice + derlig) = Controls("commande" & i)
.Range("b" & Indice + derlig) = Controls("cmb_nom" & i)
.Range("c" & Indice + derlig) = Controls("cmb_type" & Format(i, "00"))
.Range("d" & Indice + derlig) = Controls("cmb_marque" & Format(i, "00"))
.Range("e" & Indice + derlig) = Controls("txt_ref" & Format(i, "00"))
.Range("g" & Indice + derlig) = Controls("txt_eco" & Format(i, "00")) + Controls("txt_sacem" & Format(i, "00"))
.Range("h" & Indice + derlig) = Controls("cmb_produit" & Format(i, "00"))
.Range("J" & Indice + derlig) = txtsemaine.Value
If .Range("c" & Indice + derlig).Value = "Leger Garantie" _
Then .Range("f" & Indice + derlig) = Controls("txt_pv" & Format(i, "00")) + Controls("txt_sacem" & Format(i, "00")) + 22 _
Else .Range("f" & Indice + derlig) = Controls("txt_pv" & Format(i, "00")) + Controls("txt_sacem" & Format(i, "00"))
End If
.Range("i" & Indice + derlig).FormulaR1C1 = (.Range("f" & Indice + derlig).Value + .Range("g" & Indice + derlig).Value) * 1.2
If .Range("g" & Indice + derlig).Value = "" _
Then .Range("g" & Indice + derlig).Value = 0
Next
End With
With Worksheets("prepafact")
numdl = Range("a65536").End(xlUp).Row 'recuper le numero de la dernière ligne du tableau
For i = 1 To numdl
If Range("g" & i).Value = "" Then Range("g" & i).Value = "0"
Next i
End With
Columns("I:I").Selection.NumberFormat = "#,##0.00"
Unload Me
ActiveWorkbook.Save
End Sub
Private Sub cmd_annuler_Click()
Unload leger 'ferme le formulaire
End Sub |
