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
| Private Sub UserForm_Initialize()
Dim Fin As Long
With Feuil2 'ou Sheets("employes")
Fin = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Me.SpinButton1
.Min = 1
.Max = Fin + 1
End With
Me.btnmodifier.Enabled = False
Me.btnsupprimer.Enabled = False
End Sub
Private Sub T_datedenaissance_Change()
Dim Valeur As Byte
T_datedenaissance.MaxLength = 10 'nb caractères maxi autorisé dans le textbox
Valeur = Len(T_datedenaissance)
If Valeur = 2 Or Valeur = 5 Then T_datedenaissance = T_datedenaissance & "/"
End Sub
Private Sub SpinButton1_SpinDown()
With Me.SpinButton1
If .Value <= .Min + 1 Then .Value = .Max - 1
End With
End Sub
Private Sub SpinButton1_SpinUp()
With Me.SpinButton1
If .Value >= .Max Then .Value = .Min + 1
End With
End Sub
Private Sub SpinButton1_Change()
Dim i As Long
i = Me.SpinButton1.Value
If i >= 2 Then
With Feuil2
Me.T_nom = .Cells(i, 1)
Me.T_prenom = .Cells(i, 2)
Me.T_fonction = .Cells(i, 3)
Me.T_matricule = .Cells(i, 4)
Me.T_datedenaissance = .Cells(i, 5)
Me.T_adresse = .Cells(i, 6)
Me.T_codepostal = .Cells(i, 7)
Me.T_ville = .Cells(i, 8)
Me.T_pays = .Cells(i, 9)
Me.T_salairebrutannuel = .Cells(i, 10)
Me.T_numerodetelephone = .Cells(i, 11)
Me.T_numerodegsm = .Cells(i, 12)
Me.T_email = .Cells(i, 13)
End With
End If
Me.btnmodifier.Enabled = True
Me.btnsupprimer.Enabled = True
End Sub
Private Sub btncreer_Click()
Dim i As Long
With Feuil2 'ou Sheets("employes")
If Me.T_nom.Value <> "" Then
i = .Range("A" & .Rows.Count).End(xlUp).Row + 1
With Me.SpinButton1
.Max = .Max + 1
End With
.Cells(i, 1) = Me.T_nom
.Cells(i, 2) = Me.T_prenom
.Cells(i, 3) = Me.T_fonction
.Cells(i, 4) = Me.T_matricule
.Cells(i, 5) = Me.T_datedenaissance
.Cells(i, 6) = Me.T_adresse
.Cells(i, 7) = Me.T_codepostal
.Cells(i, 8) = Me.T_ville
.Cells(i, 9) = Me.T_pays
.Cells(i, 10) = Me.T_salairebrutannuel
.Cells(i, 11) = Me.T_numerodetelephone
.Cells(i, 12) = Me.T_numerodegsm
.Cells(i, 13) = Me.T_email
MsgBox "Création effectuée"
Me.SpinButton1.Value = i
Else
MsgBox "Veuillez introduire un code produit"
End If
End With
End Sub
Private Sub btnrecherche_Click()
Dim Fin As Long
Dim c As Range
If Me.T_nom.Value = "" Then
MsgBox "Introduisez un nom SVP!!"
Else
With Feuil2
Fin = .Cells(.Rows.Count, 1).End(xlUp).Row
Set c = .Range("A2:A" & Fin).Find(Me.T_nom.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Me.SpinButton1.Value = c.Row
Set c = Nothing
Else
Me.SpinButton1.Value = 1
MsgBox "Aucun résultat"
End If
End With
End If
Me.btnmodifier.Enabled = True
Me.btnsupprimer.Enabled = True
End Sub
Private Sub btnmodifier_Click()
Dim i As Long
With Feuil2 'ou Sheets("employes")
If Me.T_nom.Value <> "" Then
i = Me.SpinButton1.Value
If MsgBox("Confirmer modification de " & Me.T_nom.Value & "?", vbCritical + vbYesNo) = vbYes Then
.Cells(i, 1) = Me.T_nom
.Cells(i, 2) = Me.T_prenom
.Cells(i, 3) = Me.T_fonction
.Cells(i, 4) = Me.T_matricule
.Cells(i, 5) = Me.T_datedenaissance
.Cells(i, 6) = Me.T_adresse
.Cells(i, 7) = Me.T_codepostal
.Cells(i, 8) = Me.T_ville
.Cells(i, 9) = Me.T_pays
.Cells(i, 10) = Me.T_salairebrutannuel
.Cells(i, 11) = Me.T_numerodetelephone
.Cells(i, 12) = Me.T_numerodegsm
.Cells(i, 13) = Me.T_email
MsgBox "Modification effectuée"
Unload Me
End If
Else
MsgBox "Veuillez introduire un code produit"
End If
End With
End Sub
Private Sub btnsupprimer_Click()
Dim i As Long
With Feuil2 'ou Sheets("employes")
If Me.T_nom.Value <> "" Then
i = Me.SpinButton1.Value
If MsgBox("Confirmer suppression de " & Me.T_nom.Value & "?", vbCritical + vbYesNo) = vbYes Then
.Rows(i).Delete
Me.SpinButton1.Value = i - 1
MsgBox "Suppression effectuée"
Unload Me
End If
Else
MsgBox "Veuillez introduire un code produit"
End If
End With
End Sub
Private Sub btnquitter_Click()
Unload Me
End Sub |