Bonjour à tous,
@thev:
Merci pour ta réponse (https://forum.excel-pratique.com/viewtopic.php?p=877898#p877898) , j'en prends bonne note et tâcherai de la mettre à profit.
Pour le formulaire
FormSaisieRecherches
, rien à redire sur mon "bricolage" de la Textbox ?
TextBoxRecherche
?
Je suis certain qu'il y a moyen d'optimiser, non ?
Amicalement,
[spoiler=Spoiler]
Private Sub TextBoxRecherche_Change()
If OptionButton1 = True Then
code = Me.TextBoxRecherche.Value
If code <> "" Then
Me.ListBox1.Clear
N = 1
With Sheets("Historique_BI")
.ListObjects("TableauHistorique_BI").Range.AutoFilter
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=1, _
Criteria1:=code
For Each C In .Range("A1:A" & derlig).SpecialCells(xlCellTypeVisible)
With Me.ListBox1
.AddItem C
.List(.ListCount - 1, 1) = C.Offset(0, 1)
.List(.ListCount - 1, 2) = C.Offset(0, 12)
.List(.ListCount - 1, 3) = C.Offset(0, 21)
.List(.ListCount - 1, 4) = C.Row
End With
Next C
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=1
End With
Else
Me.ListBox1.Clear
For i = 1 To derlig
With Me.ListBox1
.AddItem Sheets("Historique_BI").Range("A" & i)
.List(ListBox1.ListCount - 1, 1) = Sheets("Historique_BI").Range("B" & i)
.List(ListBox1.ListCount - 1, 2) = Sheets("Historique_BI").Range("M" & i)
.List(ListBox1.ListCount - 1, 3) = Sheets("Historique_BI").Range("V" & i)
.List(ListBox1.ListCount - 1, 4) = i
End With
Next i
End If
End If
If OptionButton2 = True Then
code = Me.TextBoxRecherche.Value
If code <> "" Then
Me.ListBox1.Clear
With Sheets("Historique_BI")
.ListObjects("TableauHistorique_BI").Range.AutoFilter
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=2, _
Criteria1:=code
For Each C In .Range("A1:A" & derlig).SpecialCells(xlCellTypeVisible)
With Me.ListBox1
.AddItem C
.List(.ListCount - 1, 1) = C.Offset(0, 1)
.List(.ListCount - 1, 2) = C.Offset(0, 12)
.List(.ListCount - 1, 3) = C.Offset(0, 21)
.List(.ListCount - 1, 4) = C.Row
End With
Next C
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=2
End With
Else
Me.ListBox1.Clear
For i = 1 To derlig
With Me.ListBox1
.AddItem Sheets("Historique_BI").Range("A" & i)
.List(ListBox1.ListCount - 1, 1) = Sheets("Historique_BI").Range("B" & i)
.List(ListBox1.ListCount - 1, 2) = Sheets("Historique_BI").Range("M" & i)
.List(ListBox1.ListCount - 1, 3) = Sheets("Historique_BI").Range("V" & i)
.List(ListBox1.ListCount - 1, 4) = i
End With
Next i
End If
End If
If OptionButton3 = True Then
code = "*" & Me.TextBoxRecherche.Value & "*"
If code <> "" Then
Me.ListBox1.Clear
With Sheets("Historique_BI")
.ListObjects("TableauHistorique_BI").Range.AutoFilter
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=13, _
Criteria1:=code
For Each C In .Range("A1:A" & derlig).SpecialCells(xlCellTypeVisible)
With Me.ListBox1
.AddItem C
.List(.ListCount - 1, 1) = C.Offset(0, 1)
.List(.ListCount - 1, 2) = C.Offset(0, 12)
.List(.ListCount - 1, 3) = C.Offset(0, 21)
.List(.ListCount - 1, 4) = C.Row
End With
Next C
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=13
End With
Else
Me.ListBox1.Clear
For i = 1 To derlig
With Me.ListBox1
.AddItem Sheets("Historique_BI").Range("A" & i)
.List(ListBox1.ListCount - 1, 1) = Sheets("Historique_BI").Range("B" & i)
.List(ListBox1.ListCount - 1, 2) = Sheets("Historique_BI").Range("M" & i)
.List(ListBox1.ListCount - 1, 3) = Sheets("Historique_BI").Range("V" & i)
.List(ListBox1.ListCount - 1, 4) = i
End With
Next i
End If
End If
If OptionButton4 = True Then
code = "*" & Me.TextBoxRecherche.Value & "*"
If code <> "" Then
Me.ListBox1.Clear
With Sheets("Historique_BI")
.ListObjects("TableauHistorique_BI").Range.AutoFilter
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=22, _
Criteria1:=code
For Each C In .Range("A1:A" & derlig).SpecialCells(xlCellTypeVisible)
With Me.ListBox1
.AddItem C
.List(.ListCount - 1, 1) = C.Offset(0, 1)
.List(.ListCount - 1, 2) = C.Offset(0, 12)
.List(.ListCount - 1, 3) = C.Offset(0, 21)
.List(.ListCount - 1, 4) = C.Row
End With
Next C
.ListObjects("TableauHistorique_BI").Range.AutoFilter Field:=22
End With
Else
Me.ListBox1.Clear
For i = 1 To derlig
With Me.ListBox1
.AddItem Sheets("Historique_BI").Range("A" & i)
.List(ListBox1.ListCount - 1, 1) = Sheets("Historique_BI").Range("B" & i)
.List(ListBox1.ListCount - 1, 2) = Sheets("Historique_BI").Range("M" & i)
.List(ListBox1.ListCount - 1, 3) = Sheets("Historique_BI").Range("V" & i)
.List(ListBox1.ListCount - 1, 4) = i
End With
Next i
End If
End If
End Sub