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
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg, txt, contenu As String
'DEBUT
Application.ScreenUpdating = False
'si modif d'un autre champ que ceux de recherche
On Error Resume Next
If Target.Row <> 7 Or Target.Column > 6 Then
Exit Sub
'Sinon si champ de recherche non vide
ElseIf Target <> "" Then
Call Deproteger(ListeBT)
Range("TitreListeBT").Select
txt = "*" & Target.Value & "*"
Select Case Target.Name.Name 'recherche dans la liste sur colonnes
Case "rechbt1": Selection.AutoFilter Field:=1, Criteria1:=txt, Operator:=xlAnd
Case "rechbt2": Selection.AutoFilter Field:=2, Criteria1:=txt, Operator:=xlAnd
Case "rechbt3": Selection.AutoFilter Field:=3, Criteria1:=txt, Operator:=xlAnd
Case "rechbt4": Selection.AutoFilter Field:=4, Criteria1:=txt, Operator:=xlAnd
Case "rechbt5": Selection.AutoFilter Field:=5, Criteria1:=txt, Operator:=xlAnd
Case "rechbt6": txt = Mid(Target.Value, 1, 2) & "/" & Mid(Target.Value, 4, 2) & "/" & Mid(Target.Value, 9, 2)
Selection.AutoFilter Field:=6, Criteria1:=txt, Operator:=xlAnd 'traitement spécial pour date !!
End Select
Target.Select
'Sinon si champ de recherche vide
ElseIf Target = "" Then
Call Deproteger(ListeBT)
Range("TitreListeBT").Select
Select Case Target.Name.Name
Case "rechbt1": Selection.AutoFilter Field:=1
Case "rechbt2": Selection.AutoFilter Field:=2
Case "rechbt3": Selection.AutoFilter Field:=3
Case "rechbt4": Selection.AutoFilter Field:=4
Case "rechbt5": Selection.AutoFilter Field:=5
Case "rechbt6": Selection.AutoFilter Field:=6
End Select
Target.Select
End If
'FIN
Application.ScreenUpdating = True
Call Proteger(ListeBT)
End Sub |
Partager