Bonsoir
Voici 2 macros qui ont un peu la même utilité, sauf que la 1ère concerne plus de colonnes de visibles dans la ListBox
Ces macros ont été remaniées par Banzai64 il y quelques mois.
Les deux me permettent de se placer sur le thème de la cellule recherchée de manière à ce que le thème vienne se placer dans le coin supérieur de l'écran
La 2ème offre l’avantage de se placer ensuite sur la cellule elle-même.
J’ai cherché, en vain, à reproduire cet avantage sur la 1ère macro.
Je retourne donc sur le Forum, à la recherche de la solution
Merci
Option Explicit
Option Compare Text
Dim Ini As Boolean, L As Long
Private Sub Aller_Click()
End Sub
Private Sub UserForm_Activate()
Me.Left = 270
Me.Top = 130
End Sub
Private Sub ComboBox1_Change()
If Ini = False Then Exit Sub
Dim x As Range
Set x = Columns(2).Find(ComboBox1.Value, , xlValues, xlPart, , , False)
If Not x Is Nothing Then
For L = x.Row To 1 Step -1
If Left(Cells(L, 1), 5) = "Titre" Then Application.Goto Cells(L, 2), 1: Exit For
Next
End If
'ActiveSheet.CommandButton1.Top = ActiveCell.Top
Unload Me
End Sub
Private Sub ListBox1_Click()
If Ini = False Then Exit Sub
Dim x As Range
Set x = Columns(2).Find(ListBox1.Value, , xlValues, xlPart, , , False)
If Not x Is Nothing Then
For L = x.Row To 1 Step -1
If Left(Cells(L, 1), 5) = "Titre" Then Application.Goto Cells(L, 2), 1: Exit For
Next
End If
'ActiveSheet.CommandButton1.Top = ActiveCell.Top
Unload Me
End Sub
Private Sub TextBox1_ChangeOld()
If TextBox1 = "" Then Exit Sub
Dim Li As Long, Ln As Long
With ListBox1
.Clear
For L = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(L, 2) Like "*" & TextBox1 & "*" Then
If Left(Cells(L, 1), 5) <> "Titre" Then 'Le 1 désigne la colonne, Le 5 désigne la longueur du mot
ListBox1.AddItem Cells(L, 2)
For Li = L To 1 Step -1
If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): Exit For
Next
End If
End If
Next
End With
ListBox1.BackColor = &HC0FFFF
Ini = True
End Sub
Private Sub TextBox1_Change()
If TextBox1 = "" Then Exit Sub
Dim Li As Long, Ln As Long
With ListBox1
.Clear
For L = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(L, 2) Like "*" & TextBox1 & "*" Then
If Left(Cells(L, 1), 5) <> "Titre" Then 'Le 1 désigne la colonne, Le 5 désigne la longueur du mot
ListBox1.AddItem Cells(L, 2)
For Li = L To 1 Step -1
'If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): Exit For
'If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): .List(.ListIndex + Ln, 2) = Cells(L, 32): Exit For
'If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): .List(.ListIndex + Ln, 2) = Cells(L, 7): .List(.ListIndex + Ln, 3) = Cells(L, 32): Exit For
If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): .List(.ListIndex + Ln, 2) = Cells(L, 7): .List(.ListIndex + Ln, 3) = Cells(L, 9): .List(.ListIndex + Ln, 4) = Cells(L, 32): Exit For
Next
End If
End If
Next
End With
ListBox1.BackColor = &H80FF80
Ini = True
End Sub
Private Sub UserForm_Click()
MsgBox "x:" & Me.Left & vbCrLf & "y:" & Me.Top
End Sub
Option Explicit
Option Compare Text
Dim Ini As Boolean, L As Long
Private Sub ListBox1_Click()
Dim x As Range
If Ini = False Then Exit Sub
Set x = Columns(2).Find(ListBox1.Value, , xlValues, xlPart, , , False)
If Not x Is Nothing Then
Application.Goto Cells(x.Row, 2), Scroll:=True
End If
Application.Goto Cells(Me.ListBox1.Column(2), 2)
'Unload Me
' Dim x As Range
' Set x = Columns(2).Find(ListBox1.Value, , xlValues, xlPart, , , False)
' If Not x Is Nothing Then
' For L = x.Row To 1 Step -1
' If Left(Cells(L, 1), 5) = "Titre" Then Application.Goto Cells(L, 2), 1: Exit For
' Next
' End If
' 'ActiveSheet.CommandButton1.Top = ActiveCell.Top
' Unload Me
End Sub
Private Sub TextBox1_Change()
If TextBox1 = "" Then Exit Sub
Dim Li As Long, Ln As Long
With ListBox1
.Clear
.ColumnCount = 3
.ColumnWidths = "-1;-1;0"
For L = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(L, 2) Like "*" & TextBox1 & "*" Then
If Left(Cells(L, 1), 5) <> "Titre" Then
.AddItem Cells(L, 2)
For Li = L To 1 Step -1
If Left(Cells(Li, 1), 5) = "Titre" Then Ln = Ln + 1: .List(.ListIndex + Ln, 1) = Cells(Li, 2): Exit For
Next
.List(.ListCount - 1, 2) = L
End If
End If
Next
End With
Ini = True
End Sub