resultat d'une recherche par userform

'ICI C'est la mise en place initialisation
Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 10
ListBox2.ColumnCount = 10
ListBox3.ColumnCount = 10
End Sub


'ICI C'est le Moteur de Recherche
Private Sub CommandButton1_Click()
Dim C As Range, Ok As Boolean, j As Byte, Box1 As MSForms.ListBox
Dim Tablo() As String
Dim Text As String
Dim S As Byte
Dim Firstaddress As String
Dim i As Integer, X As Integer, L As Integer
Text = Me.TextBox1
If Text = "" Then Exit Sub

For S = 1 To Worksheets.Count
If Worksheets(S).Name <> "HOME" Then
        Ok = False
        If UCase(Worksheets(S).Name) = "ARMOIRES" Then
            Set Box1 = ListBox1
            Ok = True
        ElseIf UCase(Worksheets(S).Name) = "LUMINAIRES" Then i = 2
             Set Box1 = ListBox2
            Ok = True
        ElseIf UCase(Worksheets(S).Name) = "AMPOULES" Then i = 3
            Set Box1 = ListBox3
            Ok = True
        End If
        If Ok = True Then
            With Sheets(S).UsedRange
            Set C = .Find(Text, LookIn:=xlValues, LookAt:=xlPart)
            If Not C Is Nothing Then
                Firstaddress = C.Address
                Do
                      Box1.AddItem C.Offset(0, 1 - C.Column).Text
                            For X = 2 To 8
                                Box1.List(Box1.ListCount - 1, X - 1) = C.Offset(0, X - C.Column).Text
                            Next X
                   Box1.List(Box1.ListCount - 1, Box1.ColumnCount - 2) = Sheets(S).Name
                    Box1.List(Box1.ListCount - 1, Box1.ColumnCount - 1) = C.Address(0, 0)
                    i = i + 2
                Set C = .FindNext(C)
                Loop While Not C Is Nothing And C.Address <> Firstaddress
            End If
            End With
        End If
        
End If
Next S
If i = 0 Then
MsgBox "Le Texte " & Text & " n'a pas été trouvé" & vbCrLf & "Faites un essai sur une partie du nom", vbCritical, Sign
Exit Sub
End If


End Sub