Fonction recherche sur plusieurs onglets

Private Sub CommandButton1_Click()
If TextBox1 = "" Then TextBox1.SetFocus: Exit Sub
Dim dercel As Range, lig As Long, w As Worksheet, cel As Range
Application.ScreenUpdating = False
Set dercel = Sheets("résultat").Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not dercel Is Nothing Then lig = dercel.Row
For Each w In Worksheets
  If w.Name <> "résultat" Then
    For Each cel In [COLOR="Red"]w.UsedRange[/COLOR]
      If cel = TextBox1 Then 'respecte la casse, sinon => If UCase(cel) = UCase(TextBox1) Then
        lig = lig + 1
        cel.EntireRow.Copy Sheets("résultat").Rows(lig) 'ou ligne ci-dessous si l'on ne veut que les valeurs
        'Sheets("résultat").Rows(lig) = cel.EntireRow.Value
      End If
    Next cel
  End If
Next w
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
Dim lig As Long, w As Worksheet, cel As Range
Application.ScreenUpdating = False
Sheets("résultat").Rows("3:65536").ClearContents
If TextBox1 = "" Then GoTo 1
lig = 2
For Each w In Worksheets
  If w.Name <> "résultat" Then
    For Each cel In w.UsedRange
      If IIf(CheckBox1, cel = TextBox1, UCase(cel) = UCase(TextBox1)) Then
        lig = lig + 1
        Sheets("résultat").Cells(lig, 1) = w.Name
        Sheets("résultat").Cells(lig, 2).Resize(, 20) = w.Cells(cel.Row, 1).Resize(, 20).Value 'copie sur 20 colonnes
      End If
    Next cel
  End If
Next w
1 TextBox1.SetFocus
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
Dim lig As Long, w As Worksheet, cel As Range, part As String
Application.ScreenUpdating = False
Sheets("résultat").Rows("3:65536").ClearContents
If TextBox1 = "" Then GoTo 1
lig = 2
[COLOR="Red"]part = IIf(CheckBox2, "*", "")[/COLOR]
For Each w In Worksheets
  If w.Name <> "résultat" Then
    For Each cel In w.UsedRange
      If IIf(CheckBox1, cel [COLOR="red"]Like[/COLOR] part & TextBox1 & part, UCase(cel) [COLOR="red"]Like[/COLOR] part & UCase(TextBox1) & part) Then
        lig = lig + 1
        Sheets("résultat").Cells(lig, 1) = w.Name
        Sheets("résultat").Cells(lig, 2).Resize(, 20) = w.Cells(cel.Row, 1).Resize(, 20).Value 'copie sur 20 colonnes
      End If
    Next cel
  End If
Next w
1 TextBox1.SetFocus
Application.ScreenUpdating = True
End Sub

For Each cel In w.UsedRange

For Each cel In w.[COLOR="Red"][A1:H300][/COLOR]