Funzione ricerca in menù a tendina

Private Sub ComboBox1_Change()
Dim i As Long
Dim urCli As Long
Dim urForn As Long
Dim urMov As Long
urCli = Worksheets("Foglio1").Cells(Rows.Count, 2).End(xlUp).Row
urForn = Worksheets("Foglio1").Cells(Rows.Count, 3).End(xlUp).Row
urMov = Worksheets("Foglio1").Cells(Rows.Count, 4).End(xlUp).Row
Select Case Me.ComboBox1.Value
    Case Is = "Cliente"
    Me.ComboBox2.Clear
    For i = 2 To urCli
        Me.ComboBox2.AddItem Worksheets("Foglio1").Range("b" & i).Value
    Next i
    Case Is = "Fornitori"
        Me.ComboBox2.Clear
    For i = 2 To urForn
        Me.ComboBox2.AddItem Worksheets("Foglio1").Range("c" & i).Value
    Next i
    Case Is = "Movimenti"
    Me.ComboBox2.Clear
    For i = 2 To urMov
        Me.ComboBox2.AddItem Worksheets("Foglio1").Range("d" & i).Value
    Next i
End Select
End Sub



Private Sub CommandButton1_Click()
Dim i As Integer
Dim ur As Long
Dim sh As Worksheet
Set sh = Worksheets("Gennaio 2017")
ur = UltimaRiga(Worksheets("Gennaio 2017").Range("a2:a33"))
sh.Cells(ur + 1, 1).Value = CDate(Me.TextBox1.Value)
sh.Cells(ur + 1, 2).Value = Me.ComboBox1.Value
sh.Cells(ur + 1, 3).Value = Me.ComboBox2.Value
For i = 2 To 8
    sh.Cells(ur + 1, i + 2).Value = Me.Controls("TextBox" & i).Value
Next i
End Sub

Private Sub CommandButton2_Click()
Me.Hide
End Sub




Private Sub UserForm_Initialize()
Dim i As Long
Dim urList As Long
Dim urCli As Long
Dim urForn As Long
Dim urMov As Long
urList = Worksheets("Foglio1").Cells(Rows.Count, 1).End(xlUp).Row
urCli = Worksheets("Foglio1").Cells(Rows.Count, 2).End(xlUp).Row
urForn = Worksheets("Foglio1").Cells(Rows.Count, 3).End(xlUp).Row
urMov = Worksheets("Foglio1").Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To urList
    Me.ComboBox1.AddItem Worksheets("Foglio1").Range("a" & i).Value
Next i
End Sub