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
| Private Sub UserForm_Initialize()
'Indications sur le format de remplissage lors de l'ouverture du formulaire
With NewQueryUserForm.TextBox9
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox3
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox8
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox2
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox6
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox10
.Text = "pdt/_ _ _ / _ _ _"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox8
.Text = "dd/mm/yyyy"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
With NewQueryUserForm.TextBox11
.Text = "hh:mm"
.ForeColor = RGB(160, 160, 160)
.Font.Size = 10
End With
End Sub
'Bouton CREATE dans formulaire
Private Sub CommandButton1_Click()
Dim Ligne As Long
Ligne = ActiveSheet.Range("A65536").End(xlUp).Row + 1
'Vérification des autres champs obligatoires : textbox avec dates 9 & 6, textbox 4& 5& 7, ComboBox 1& 2& 3
If TextBox9 = "" Or TextBox6 = "" Or TextBox4 = "" Or TextBox5 = "" Or TextBox7 = "" Or ComboBox1.ListIndex < 0 Or ComboBox2.ListIndex < 0 Or ComboBox3.ListIndex < 0 Then
MsgBox "Please fill all the mandatory fields"
'vérification format dates pour les champs obligatoires 9 et 6
If Not IsDate(NewQueryUserForm.TextBox9.Text) Then
MsgBox "The Data entered is not a Date" _
& Chr(10) & "Please Try again with the format dd/mm/yyyy"
ElseIf IsDate(NewQueryUserForm.TextBox6.Text) Then
MsgBox "The Data entered is not a Date" _
& Chr(10) & "Please Try again with the format dd/mm/yyyy"
'Ligne manquante: Si OptionButton 1 ou 2 coché alors:
'ligne manquante: vérifier que TextBox11 est au format "hh:mm"
'ligne manquante: vérifier que TextBox10 est au format "pdt/_ _ _ / _ _ _"
If Not IsDate(NewQueryUserForm.TextBox8.Text) Then
MsgBox "The Data entered is not a Date" _
& Chr(10) & "Please Try again with the format dd/mm/yyyy"
If Not IsDate(NewQueryUserForm.TextBox2.Text) Then
MsgBox "The Data entered is not a Date" _
& Chr(10) & "Please Try again with the format dd/mm/yyyy"
'Créer la nouvelle ligne d'enregistrement
ElseIf Cells(Ligne, 1) <> "" Then
With ThisWorkbook.Sheets("Query Log")
ComboBox1 = .Cells(Ligne, 3)
ComboBox2 = .Cells(Ligne, 6)
ComboBox3 = .Cells(Ligne, 15)
TextBox1 = .Cells(Ligne, 11)
TextBox3 = .Cells(Ligne, 11)
TextBox10 = .Cells(Ligne, 7)
TextBox8 = .Cells(Ligne, 9)
TextBox11 = .Cells(Ligne, 10)
TextBox4 = .Cells(Ligne, 17)
TextBox5 = .Cells(Ligne, 18)
TextBox6 = .Cells(Ligne, 20)
TextBox7 = .Cells(Ligne, 19)
End With
'Product serviceable Y/N ? Case à cocher
If OptionButton3.Value = True Then
Worksheet("Query Log").Cells(newrow, 13).Value = "Serviceable"
End If
If OptionButton2.Value = True Then
Worksheet.Cells(newrow, 13).Value = "Unserviceable"
End If
'fermer après enregistremet du formulaire
NewQueryUserForm.Hide
End If
End Sub
'Bouton CANCEL NewQueryUserForm
Private Sub CommandButton2_Click()
If MsgBox("Do you want to cancel the new query ?", vbYesNo, "Exit confirmation") = vbYes Then
NewQueryUserForm.Hide
End If
End Sub |
