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 |
Partager