Completamento automatico

Option Explicit

Option Compare Text

Private sh As Worksheet

Private Sub ListBox1_Click()
ActiveCell.Value = UserForm1.ListBox1.Value
UserForm1.TextBox1.Value = ""
UserForm1.Hide
End Sub

Private Sub TextBox1_Change()
Call mCaricaListBox("FiltraDati")
End Sub

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("listini") <=== Questa la riga da modificare inserendo il nome del nuovo foglio tra le virgolette.
    Call mCaricaListBox("CaricaDati")
End Sub


Private Sub mCaricaListBox(ByVal s As String)

    Dim lRiga As Long
    Dim lng As Long
    
    With sh
        lRiga = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
    
    With Me.ListBox1
        If s = "CaricaDati" Then
            For lng = 3 To lRiga
                .AddItem (sh.Range("A" & lng).Value)
            Next
        ElseIf s = "FiltraDati" Then
            .Clear
            For lng = 3 To lRiga
                If InStr(sh.Range("A" & lng).Value, Me.TextBox1.Text) Then
                    .AddItem sh.Range("A" & lng).Value
                End If
            Next
        End If
    End With
    
End Sub


Private Sub UserForm_Terminate()
    Set sh = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("a22:a40")) Is Nothing Then
    If Target.Value <> "" Then Exit Sub
    UserForm1.Show
End If
End Sub