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
| Option Explicit 'oblige à déclarer toutes les variables
Private Sub ComboBox1_Change()
End Sub
Private Sub ComboBox1_Click()
Dim i As Byte
CommandButton3.Visible = True
CommandButton4.Visible = True
Label27.Caption = "mode : Modification"
With ComboBox1
For i = 1 To 14
Controls("TextBox" & i) = Sheets("Liste").Cells(.List(.ListIndex, 1), i)
Next i
End With
End Sub
Private Sub CommandButton1_Click() 'bouton "Ok"
Dim Dl As Long 'déclare la variable Dl (Dernière ligne)
Dim x As Byte 'déclare la variable x
With Sheets("Liste")
.Activate 'active l'onglet "Liste"
Dl = .Range("A65536").End(xlUp).Row + 1 'définit la variable Dl
'place les donnés dans le tableau
'On vérifie que les champs obligatoires sont correctement remplis
If TextBox1.Text = "" Then
MsgBox "Veuillez entrer la date du devis", vbCritical, "Champs manquant"
TextBox1.SetFocus
ElseIf TextBox2.Text = "" Then
MsgBox "Veuillez remplir le nom du patient", vbCritical, "Champs manquant"
TextBox2.SetFocus
ElseIf TextBox7.Text = "" Then
MsgBox "Veuillez entrer le nombre de devis", vbCritical, "Champs manquant"
TextBox7.SetFocus
ElseIf TextBox8.Text = "" Then
MsgBox "Veuillez entrer le montant du devis", vbCritical, "Champs manquant"
TextBox8.SetFocus
Else
For x = 1 To 14 'boucle sur 14 éléments
.Cells(Dl, x).Value = Me.Controls("TextBox" & x).Value
Next x
Unload Me 'vide et ferme l'UserFOrm
End If
'tri selon le nom
.Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
Private Sub CommandButton2_Click() 'bouton "Annuler"
Unload Me 'vide et ferme l'UserFOrm
End Sub
Private Sub CommandButton3_Click()
Dim i As Byte
With ComboBox1
For i = 1 To 14
Sheets("Liste").Cells(.List(.ListIndex, 1), i) = Controls("TextBox" & i)
Controls("TextBox" & i) = ""
Next i
.ListIndex = -1
End With
CommandButton3.Visible = False
CommandButton4.Visible = False
Label27.Caption = "mode : création"
initialisecombo
End Sub
Private Sub CommandButton4_Click()
Dim i As Byte
With ComboBox1
For i = 1 To 14
Controls("TextBox" & i) = ""
Next i
.ListIndex = -1
End With
CommandButton3.Visible = False
CommandButton4.Visible = False
Label27.Caption = "mode : création"
End Sub
Private Sub CommandButton5_Click() 'bouton "Fermer"
Unload Me 'vide et ferme l'UserForm
End Sub
Private Sub UserForm_Initialize()
Label27.Caption = "mode : création"
initialisecombo
End Sub
Public Sub initialisecombo()
Dim i As Integer
With ComboBox1
.Clear
.ColumnCount = 2
.ColumnWidths = "50;0"
End With
With Sheets("Liste")
For i = 2 To .Range("a65536").End(xlUp).Row
'Afficher Colonnes Nom Prénom et Date dans ComboBox1
ComboBox1.AddItem
ComboBox1.List(ComboBox1.ListCount - 1, 0) = .Cells(i, 2) & " " & .Cells(i, 3) & " - " & .Cells(i, 1)
ComboBox1.List(ComboBox1.ListCount - 1, 1) = i
Next i
End With
End Sub |
