Bonsoir le fil, bonsoir le forum,
En pièce jointe ton fichier modifié avec le code ci-dessous :
Private OS As Worksheet
Private TS As ListObject
Private OD As Worksheet
Private TD As ListObject
Private Sub UserForm_Initialize()
Set OS = Worksheets("Registre")
Set TS = OS.ListObjects("Tableau3")
Set OD = Worksheets("Secteurs")
Set TD = OD.ListObjects("Tableau1")
End Sub
Private Sub btnAjout_Click()
Dim R As Range
Dim LI As Integer
Dim CTRL As Control
Dim COL As Integer
Reponse1 = MsgBox("Enregistrer la saisie?", vbYesNo + vbQuestion, "DEMANDE DE CONFIRMATION D'AJOUT")
If Reponse1 = vbYes Then
Set R = TS.ListColumns(1).Range.Find("")
If R Is Nothing Or TS.ListRows.Count = 0 Then
TS.ListRows.Add
LI = TS.ListRows.Count
Else
LI = R.Row - TS.HeaderRowRange.Row
End If
For Each CTRL In Me.Controls
If CTRL.Tag <> "" Then
TS.DataBodyRange(LI, CInt(CTRL.Tag)).Value = CTRL.Value
End If
Next CTRL
End If
COL = TD.HeaderRowRange.Find(Me.cboSecteur.Value, , xlValues, xlWhole).Column
Set R = TD.ListColumns(COL).Range.Find("")
If R Is Nothing Or TD.ListRows.Count = 0 Then
TD.ListRows.Add
LI = TD.ListRows.Count
Else
LI = R.Row - TD.HeaderRowRange.Row
End If
TD.DataBodyRange(LI, COL).Value = Me.txtNom.Value
ActiveWorkbook.RefreshAll
Unload Me
ActiveWorkbook.Save
End Sub
Private Sub BtnAnnuler_Click()
Reponse2 = MsgBox("Annulation de la saisie?", vbYesNo + vbQuestion, "DEMANDE DE CONFIRMATION D'ANNULATION")
If Reponse2 = vbYes Then
Unload Me
FrmSaisieRegistre.Show
End If
End Sub
Private Sub btnExit_Click()
ActiveWorkbook.RefreshAll
Unload Me
ActiveWorkbook.Save
End Sub
Le fichier :