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 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197
| Option Explicit 'Activation de la déclaration explicite des variables
Dim Ws As Worksheet 'Variable pour un Objet Worksheet en PUBLIC pour tous les Controls de cet UserForm
'Correspond au programme du FORMULAIRE
Private Sub UserForm_Initialize()
Dim J As Long
Dim I As Integer
Set Ws = Sheets("Listing intermédiaires")
With Me.ComboBoxNOM
For J = 2 To Ws.Range("E" & Rows.Count).End(xlUp).Row
.AddItem Ws.Range("E" & J)
Next J
End With
End Sub
'Correspond au programme de la LISTE DEROULANTE
Private Sub ComboBoxNOM_Change()
Dim Ligne As Long
Dim I As Integer
If ComboBoxNOM.ListIndex <> -1 Then
Ligne = ComboBoxNOM.ListIndex + 2
TextBoxBureau.Text = Ws.Cells(Ligne, "A").Value
TextBoxNom.Text = Ws.Cells(Ligne, "E").Value
TextBoxPrenom.Text = Ws.Cells(Ligne, "F").Value
TextBoxDate.Text = Ws.Cells(Ligne, "G").Value
TextBoxNumero.Text = Ws.Cells(Ligne, "B").Value
'Page Statut
TextBoxStatut.Text = Ws.Cells(Ligne, "H").Value
CheckBoxFR.Value = Ws.Cells(Ligne, "D").Value = "FR"
CheckBoxNL.Value = Ws.Cells(Ligne, "D").Value = "NL"
CheckBoxPV.Value = Ws.Cells(Ligne, "C").Value = "PV"
CheckBoxSIR.Value = Ws.Cells(Ligne, "C").Value = "SIR"
TextBoxInscription.Text = Ws.Cells(Ligne, "P").Value
'Page Formations
CheckBox2002.Value = Ws.Cells(Ligne, "I") <> "Inexistant"
If Ws.Cells(Ligne, "I") <> "Inexistant" Then Me.Controls("TextBoxCESS") = Ws.Cells(Ligne, "I")
CheckBoxPost2002 = Ws.Cells(Ligne, "J") <> "Inexistant"
If Ws.Cells(Ligne, "J") <> "Inexistant" Then Me.Controls("TextboxCESS") = Ws.Cells(Ligne, "I")
TextBoxDispense.Text = Ws.Cells(Ligne, "K").Value
CheckBoxLoiYES.Value = Ws.Cells(Ligne, "L") = "OK"
CheckBoxLoiNO.Value = Ws.Cells(Ligne, "L") = "NOK"
CheckBoxMifidYES.Value = Ws.Cells(Ligne, "M") = "OK"
CheckBoxMifidNO.Value = Ws.Cells(Ligne, "M") = "NOK"
CheckBoxIardYES.Value = Ws.Cells(Ligne, "N") = "OK"
CheckBoxIardNO.Value = Ws.Cells(Ligne, "N") = "NOK"
CheckBoxVieYES.Value = Ws.Cells(Ligne, "O") = "OK"
CheckBoxVieNO.Value = Ws.Cells(Ligne, "O") = "NOK"
'Page FSMA
CheckBoxFormYES.Value = Ws.Cells(Ligne, "Q") <> "N/A"
CheckBoxFormNO.Value = Ws.Cells(Ligne, "Q") = "N/A"
TextBoxForm.Text = Ws.Cells(Ligne, "Q").Value
CheckBoxAdhesionYES.Value = Ws.Cells(Ligne, "R") <> "N/A"
CheckBoxAdhesionNO.Value = Ws.Cells(Ligne, "R") = "N/A"
TextBoxAdhesion.Text = Ws.Cells(Ligne, "R").Value
CheckBoxRcYES.Value = Ws.Cells(Ligne, "W") <> "N/A"
CheckBoxRcYES.Value = Ws.Cells(Ligne, "W") <> "Inexistant"
CheckBoxRcNO.Value = Ws.Cells(Ligne, "W") = "Inexistant"
CheckBoxRcNO.Value = Ws.Cells(Ligne, "W") = "N/A"
TextBoxRc.Text = Ws.Cells(Ligne, "W").Value
CheckBoxBvmYES.Value = Ws.Cells(Ligne, "X") <> "Inexistant"
CheckBoxBvmNO.Value = Ws.Cells(Ligne, "X") = "Inexistant"
TextBoxBvm.Text = Ws.Cells(Ligne, "X").Value
CheckBoxForm117YES.Value = Ws.Cells(Ligne, "S") = "OK"
CheckBoxForm117NO.Value = Ws.Cells(Ligne, "S") = "NOK"
CheckBoxForm118YES.Value = Ws.Cells(Ligne, "T") = "OK"
CheckBoxForm118NO.Value = Ws.Cells(Ligne, "T") = "NOK"
CheckBoxExpYES.Value = Ws.Cells(Ligne, "V") = "OK"
CheckBoxExpNO.Value = Ws.Cells(Ligne, "V") = "NOK"
TextBoxRemarques.Text = Ws.Cells(Ligne, "Z").Value
End If
End Sub
'Pour le bouton Nouveau
Private Sub CommandButtonNOUVEAU_Click()
Dim L As Integer
If MsgBox("Confirmez-vous linsertion de ce nouvel intermédiaire ?", vbYesNo, "Demande de confirmation dajout") = vbYes Then
L = Sheets("Listing intermédiaires").Range("a65536").End(xlUp).Row + 1
Range("A" & L).Value = TextBoxBureau.Text
Range("B" & L).Value = TextBoxNumero.Text
If CheckBoxPV = True Then Range("C" & L).Value = "PV"
If CheckBoxSIR = True Then Range("C" & L).Value = "SIR"
If CheckBoxFR = True Then Range("D" & L).Value = "FR"
If CheckBoxNL = True Then Range("D" & L).Value = "NL"
Range("E" & L).Value = TextBoxNom.Text
Range("F" & L).Value = TextBoxPrenom.Text
Range("G" & L).Value = TextBoxDate.Text
Range("H" & L).Value = TextBoxStatut.Text
If CheckBox2002 = True Then Range("I" & L).Value = TextBox14.Text
If CheckBoxPost2002 = True Then Range("J" & L).Value = TextBox14.Text
Range("K" & L).Value = TextBoxDispense.Text
If CheckBoxLoiYES = True Then Range("L" & L).Value = "OK"
If CheckBoxLoiNO = True Then Range("L" & L).Value = "NOK"
If CheckBoxMifidYES = True Then Range("M" & L).Value = "OK"
If CheckBoxMifidNO = True Then Range("M" & L).Value = "NOK"
If CheckBoxIardYES = True Then Range("N" & L).Value = "OK"
If CheckBoxIardNO = True Then Range("N" & L).Value = "NOK"
If CheckBoxVieYES = True Then Range("O" & L).Value = "OK"
If CheckBoxVieNO = True Then Range("O" & L).Value = "NOK"
Range("P" & L).Value = TextBoxInscription.Text
If CheckBoxFormYES = True Then Range("Q" & L).Value = TextBoxForm.Text
If CheckBoxFormNO = True Then Range("Q" & L).Value = TextBoxForm.Text
If CheckBoxAdhesionYES = True Then Range("R" & L).Value = TextBoxAdhesion.Text
If CheckBoxAdhesionNO = True Then Range("R" & L).Value = TextBoxAdhesion.Text
If CheckBoxForm117YES = True Then Range("S" & L).Value = "OK"
If CheckBoxForm117NO = True Then Range("S" & L).Value = "NOK"
If CheckBoxForm118YES = True Then Range("T" & L).Value = "OK"
If CheckBoxForm118NO = True Then Range("T" & L).Value = "NOK"
If CheckBoxExpYES = True Then Range("V" & L).Value = "OK"
If CheckBoxExpNO = True Then Range("V" & L).Value = "NOK"
If CheckBoxRcYES = True Then Range("W" & L).Value = TextBoxRc.Text
If CheckBoxRcNO = True Then Range("W" & L).Value = TextBoxRc.Text
If CheckBoxBvmYES = True Then Range("X" & L).Value = TextBoxBvm.Text
If CheckBoxBvmNO = True Then Range("X" & L).Value = TextBoxBvm.Text
Range("Z" & L).Value = TextBoxRemarques.Text
End If
End Sub
'Correspond au programme du bouton MODIFIER
Private Sub CommandButtonMODIFIER_Click()
Dim Ligne As Long
Dim I As Integer
If MsgBox("Etes-vous certain de vouloir modifier cet intermédiaire ?", vbYesNo, "Demande de confirmation") = vbYes Then
If Me.ComboBoxNOM.ListIndex = -1 Then Exit Sub 'On sort si pas de sélection
Ligne = Me.ComboBoxNOM.ListIndex + 2
For I = 1 To 14
If Me.Controls("TextBox" & I).Visible = True Then
Ws.Cells(Ligne, I + 1) = Me.Controls("TextBox" & I)
End If
Next I
End If
End Sub
' Correspond au bouton SUPPRIMER
Private Sub CommandButtonSUPPRIMER_Click()
Dim L As Long
If MsgBox("Etes-vous certain de vouloir supprimer cet intermédiaire ?", vbYesNo, "Demande de confirmation") = vbYes Then
If Me.ComboBoxNOM.ListIndex = -1 Then Exit Sub
L = Me.ComboBoxNOM.ListIndex + 2
Ws.Rows(L).Delete
End If
End Sub
'Correspond au programme du bouton QUITTER
Private Sub CommandButtonQUITTER_Click()
Unload Me
End Sub |
Partager