Combobox dans userform possibilité de chercher

Bonjour a tous,

Je suis profane de VBA et je suis en train d'essayer d'apprendre , j'aimerais créer une liste déroulante (Combobox) sur userform avec une procédure VBA afin de pouvoir faire une liste déroulante qui me donne la liste des choix selon le texte que j'écris sur la combobox , les information sont situé sur la colonne B de la feuil (fournisseur), j'ai essayer de demander l'aide de chatgpt mais je rencontre un probleme , regarder le code que j'ai obtenu , mais quand je valide je reçoie un message d'erreur en jaune sur la toute première ligne de code (Private sub userform_initialize (),

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim cell As Range

    'Specify the worksheet and column to get data from
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Assuming data is in column A
    Set dataRange = ws.Range("A1:A" & lastRow)

    'Populate the ComboBox with data from the specified column
    For Each cell In dataRange
        cmbSearchable.AddItem cell.Value
    Next cell

    'Set the ComboBox to be searchable
    cmbSearchable.Style = fmStyleDropDownList
    cmbSearchable.AutoComplete = True
    cmbSearchable.MatchRequired = True
End Sub

Private Sub cmbSearchable_Change()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim cell As Range
    Dim searchTerm As String

    'Specify the worksheet and column to get data from
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Assuming data is in column A
    Set dataRange = ws.Range("A1:A" & lastRow)

    'Clear the ComboBox
    cmbSearchable.Clear

    'Populate the ComboBox with filtered data based on the search term
    searchTerm = cmbSearchable.Text
    For Each cell In dataRange
        If InStr(1, cell.Value, searchTerm, vbTextCompare) > 0 Then
            cmbSearchable.AddItem cell.Value
        End If
    Next cell

    'Select the first item in the ComboBox
    If cmbSearchable.ListCount > 0 Then
        cmbSearchable.ListIndex = 0
    End If
End Sub

si vous pouvez m'aider ce sera très appréciable,

cordialement,

salim.

Bonjour,

Exemple de code :

Option Explicit

Dim dataRange As Range

Private Sub UserForm_Initialize()
    Dim lastRow As Long

    'Specify the worksheet and column to get data from
    With Feuil1
        lastRow = .Cells.Find("*", , , , , xlPrevious).Row
        Set dataRange = .Range("A1:A" & lastRow)
    End With

    'Populate the ComboBox with data from the specified column
    cmbSearchable.List = dataRange.Value

    'Set the ComboBox to be searchable
    cmbSearchable.MatchEntry = 2
    cmbSearchable.MatchRequired = True

End Sub

Private Sub cmbSearchable_Change()
    Dim cell As Range
    Dim searchTerm As String

    With cmbSearchable

        If .ListIndex = -1 Then
            'Clear the ComboBox
             .Clear

            'Populate the ComboBox with filtered data based on the search term
            searchTerm = .Text
            For Each cell In dataRange
                If InStr(1, cell.Value, searchTerm, vbTextCompare) > 0 Then
                    .AddItem cell.Value
                End If
            Next cell

            'Display items in the ComboBox
            .DropDown
        End If

    End With

End Sub