Option Compare Text 'la casse est ignorée
Dim P As Range 'mémorise la variable
Private Sub ListBox1_Click()
Dim n%
TextBox2 = P(ListBox1) 'propriété BoundColumn = 1
TextBox2.SetFocus
n = InStr(TextBox2, TextBox1)
If n Then
TextBox2.SelStart = n - 1
TextBox2.SelLength = Len(TextBox1)
Else
TextBox2.SelStart = 0
ListBox1.SetFocus
End If
Label4 = "Nombre de caractères : " & Len(TextBox2)
End Sub
Private Sub OptionButton1_Change()
TextBox1.SetFocus: TextBox1_Change
End Sub
Private Sub TextBox1_Change()
Dim x$, tablo, op As Boolean, i&, test As Boolean, n&, a()
ListBox1.Clear: TextBox2 = "": Label4 = "Nombre de caractères : 0" 'RAZ
Set P = Intersect(Feuil1.[A:A], Feuil1.UsedRange) 'Feuil1 => CodeName
If TextBox1 <> "" And Not P Is Nothing Then
x = Replace(TextBox1, "[", "[[]") 'remplacement du crochet gauche
x = "*" & x & "*"
tablo = P.Resize(, 2) 'au moins 2 éléments
op = OptionButton1
For i = 2 To UBound(tablo)
If op Then test = tablo(i, 1) Like x Else test = tablo(i, 1) <> "" And Not tablo(i, 1) Like x
If test Then
n = n + 1
ReDim Preserve a(1 To 2, 1 To n)
a(1, n) = i 'numéro de ligne dans la 1ère colonne
a(2, n) = Left(tablo(i, 1), 100)
End If
Next
If n = 1 Then
ListBox1.AddItem a(1, 1): ListBox1.List(0, 1) = a(2, 1)
ElseIf n Then
ListBox1.List = Application.Transpose(a) 'maximum 65536 lignes avec la fonction Transpose
End If
End If
Label2 = "Nombre de lignes : " & ListBox1.ListCount
End Sub