Private Sub ComboBox1_Change()

ComboBox1.RowSource = ""

Me.ComboBox1.Clear

Dim aaa As Range

Set aaa = Sheet1.Range("A1:a15")

Dim aa As String

aa = ComboBox1.Text

If aa = "" Then GoTo 0

   With aaa

      b = .Rows.Count

      c = .Columns.Count

         Set a = Range(.Cells(1, 1), .Cells(.Rows.Count, .Columns.Count)).Find(aa)

         If Not a Is Nothing Then

         f = a.Address

         Do

            ComboBox1.AddItem a

         Set a = Range(.Cells(1, 1), .Cells(b, c)).FindNext(a)

         Loop While Not a Is Nothing And a.Address <> f

         End If

  End With

Set a = Nothing

GoTo 1

0

ComboBox1.RowSource = "sheet1!a1:a" & Sheet1.Cells(Rows.Count, "a").End(xlUp).Row

1

End Sub

Private Sub UserForm_Initialize()

ComboBox1.RowSource = "sheet1!a1:a" & Sheet1.Cells(Rows.Count, "a").End(xlUp).Row

End Sub