Chercher mot dans une feuille, afficher résultat sur listBox

Bonjour;

Je tiens vous remercie d’avance de votre soutien habituel

La problématique est que: mon CommandButton, cherche que la première colonne A, je voudrais que ma recherche soit dans toute la feuille BDD, merci.

Private Sub CommandButton4_Click()
i = 0
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 2
Me.ListBox1.BoundColumn = 2
Me.ListBox1.ColumnWidths = "0;150"
With Sheets("BDD")
.AutoFilterMode = False
Set plage = .Range(.[A2], .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 1)
plage.AutoFilter 1, "*" & Me.TextBox14 & "*"
Set plage = plage.Offset(1).Resize(plage.Rows.Count - 1)
If Application.Subtotal(103, plage) > 0 Then
Set plage = plage.Resize(, 1).SpecialCells(xlCellTypeVisible)
For Each C In plage
Me.ListBox1.AddItem C.Row
Me.ListBox1.List(i, 1) = C.Value
i = i + 1
Next C
End If
.AutoFilterMode = False
End With
Me.TextBox12 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox8 = ""
Me.OptionButton9.Value = False
Me.OptionButton10.Value = False
Me.OptionButton8.Value = False
Me.OptionButton7.Value = False
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton4.Value = False

For i = 0 To N - 1
If ListBox.Selected(i) Then
MsgBox "la valeur suivante est sélectionnée : " & ListBox.List(i)
End If
Next i

If ListBox1.ListCount = 0 Then
MsgBox "Ce matériel n'existe pas dans la base de données"
End If

End Sub

salutations.

98preparateur.xlsm (215.46 Ko)

la repense:

Private Sub CommandButton4_Click()

Dim Prem As String
Dim c As Range

With Me.ListBox1
    .Clear
    .ColumnCount = 2
    .BoundColumn = 2
    .ColumnWidths = "0;150"
End With

With Worksheets("BDD").UsedRange
    Set c = .Find(Me.TextBox14, LookIn:=xlValues, Lookat:=xlPart)
    If Not c Is Nothing Then
        Prem = c.Address
        Do
            With Me.ListBox1
                .AddItem c.Row
                .List(.ListCount - 1, 1) = c.Offset(, 1 - c.Column)
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> Prem
    End If
End With

Me.TextBox12 = ""
Me.TextBox14 = ""
Me.TextBox15 = ""
Me.TextBox16 = ""
Me.TextBox8 = ""
Me.OptionButton9.Value = False
Me.OptionButton10.Value = False
Me.OptionButton8.Value = False
Me.OptionButton7.Value = False
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton4.Value = False
Me.OptionButton11.Value = False

If ListBox1.ListCount = 0 Then
MsgBox "Ce matériel n'existe pas dans la base de données"
End If

End Sub

Salutations

Rechercher des sujets similaires à "chercher mot feuille afficher resultat listbox"