Private Sub Worksheet_Change(ByVal Target As Range)
Dim DLig As Long
' Vérifier que l'on fait bien un changement dans la colonne B
If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub
'
If Target.Value <> "" Then
With Sheets("Liste")
DLig = .Range("A" & Rows.Count).End(xlUp).Row
.Range("B2").Value = "*" & Target.Value & "*"
.Range("A1:A" & DLig).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("B1:B2"), CopyToRange:=.Range("C1"), _
Unique:=False
DLig = .Range("C" & Rows.Count).End(xlUp).Row
End With
On Error Resume Next
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=ChoixMissions"
If Err.Number <> 0 Then
MsgBox "Ce terme n'a pas été trouvé dans la liste !" & vbCr _
& "Choix sur la liste globale", vbInformation, "OUPS ...."
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ListeMissions"
End If
.ShowError = False
End With
On Error GoTo 0
Else
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=ListeMissions"
.ShowError = False
End With
End If
Target.Select
End Sub