re
Je reconnais que c'est du bricolage j'apprend au fur et a mesure.
la macro ce trouve dans l'userform1 et Private Sub CommandButton2_Click
Voici de code
Private Sub CommandButton2_Click()
Dim Plg As Range
Dim Nb As Boolean, Nb1 As Boolean, Nb2 As Boolean
tout = ComboBox2.Value & DTPicker1.Value & TextBox4.Value
For N = 3 To Range("A" & Rows.Count).End(xlUp).Row
If Range("C" & N) & Range("F" & N) & Range("N" & N) = tout Then
MsgBox "toto"
Exit Sub
End If
Next
If Trim(Me.ComboBox2) = "" Or Trim(Me.TextBox3) = "" Then
MsgBox "Le Site et La Référence sont des données obligatoires"
Exit Sub
End If
Set Plg = Sheets("pilotage").Cells(Rows.Count, 1).End(xlUp)(2)
Plg = UserForm1.TextBox2 'aurore
Plg.Offset(, 0) = UserForm1.ComboBox2
Plg.Offset(, 1) = UserForm1.TextBox2.Value 'DT
Plg.Offset(, 2) = UserForm1.ComboBox2 'SITE
Plg.Offset(, 7) = UserForm1.TextBox27 'NON VALIDATION
Plg.Offset(, 4) = UserForm1.TextBox3.Value 'reference
Plg.Offset(, 5) = UserForm1.DTPicker1 'date evenement
Plg.Offset(, 6) = UserForm1.TextBox1 'HEURE
Plg.Offset(, 3) = UserForm1.DTPicker2 'MAIL
Plg.Offset(, 8) = UserForm1.DTPicker3 'DATE SAISIE
Plg.Offset(, 9) = UserForm1.ComboBox4 'RISQUE
Plg.Offset(, 10) = UserForm1.ComboBox3 'TYPE
Plg.Offset(, 11) = UserForm1.ComboBox23
Plg.Offset(, 12) = UserForm1.ComboBox5 'LIEUX
Plg.Offset(, 13) = UserForm1.TextBox4 '
Plg.Offset(, 14) = UserForm1.TextBox5 '
Plg.Offset(, 15) = UserForm1.TextBox6 '
Plg.Offset(, 16) = UserForm1.TextBox7 '
Plg.Offset(, 17) = UserForm1.TextBox8 '
Plg.Offset(, 18) = UserForm1.TextBox9 '
'victime
Plg.Offset(, 19) = UserForm1.TextBox10 '
Plg.Offset(, 20) = UserForm1.TextBox11 '
Plg.Offset(, 21) = UserForm1.TextBox12 '
Plg.Offset(, 22) = UserForm1.TextBox13 '
Plg.Offset(, 23) = UserForm1.TextBox14 '
Plg.Offset(, 24) = UserForm1.TextBox15 '
Plg.Offset(, 25) = UserForm1.TextBox16 '
Plg.Offset(, 26) = UserForm1.TextBox17 '
'auteur
Plg.Offset(, 27) = UserForm1.TextBox18 '
Plg.Offset(, 28) = UserForm1.ComboBox7 '
Plg.Offset(, 29) = UserForm1.TextBox20 'identifiant
Plg.Offset(, 30) = UserForm1.ComboBox6
'INTERVENANT
Plg.Offset(, 31) = UserForm1.ComboBox10
Plg.Offset(, 32) = UserForm1.ComboBox11
Plg.Offset(, 33) = UserForm1.ComboBox12
Plg.Offset(, 34) = UserForm1.ComboBox13
Plg.Offset(, 35) = UserForm1.ComboBox14
Plg.Offset(, 36) = UserForm1.ComboBox15
Plg.Offset(, 37) = UserForm1.ComboBox8 'ACTION1
Plg.Offset(, 38) = UserForm1.ComboBox9 'ACTION2
Plg.Offset(, 40) = UserForm1.DTPicker4
Plg.Offset(, 41) = UserForm1.ComboBox16
Plg.Offset(, 42) = UserForm1.ComboBox17
Plg.Offset(, 43) = UserForm1.ComboBox18
Plg.Offset(, 44) = UserForm1.ComboBox19
Plg.Offset(, 45) = UserForm1.ComboBox20
Plg.Offset(, 46) = UserForm1.ComboBox21
Plg.Offset(, 47) = UserForm1.ComboBox22
'verifie sur feuille pilotage
If Application.WorksheetFunction.CountIf(Range("AD:AD"), TextBox20.Value) > 1 Then
MsgBox "Ce nom a déjà été saisi EN 2018 "
'verifie sur feuille 2017
Dim ws As Worksheet
Dim rng As Range
Dim x As Double
Set ws = ActiveWorkbook.Worksheets("2017")
Set rng = ws.Range("AD:AD")
If TextBox20 <> "" Then x = WorksheetFunction.CountIf(rng, TextBox20.Value) Else x = 0
MsgBox "Ce nom a déjà été saisi " & x & " fois en 2017", 64, "Attention !..."
End If
Unload Me
UserForm1.Show
If Me.ComboBox2.ListIndex = -1 Then
Sheets("Feuil2").Cells(1, 1).End(xlDown).Offset(1, 0).Value = Me.ComboBox2.Value
Sheets("Feuil2").Cells(1, 2).End(xlDown).Offset(1, 0).Value = Me.TextBox2.Value
Call Macro1
End If
End Sub
Merci encore de ta patience et de ton aide.