--- place the text between --- In the code For sheet1 
Option Explicit 
Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Target.Column = 1 Then 
        UserForm1.Show 
    End If 
     
End Sub 
--- End of place this In sheet1 
--- place the text between --- In a module 
Option Explicit 
Function xlLastRow(Optional WorksheetName As String) As Long 
     
     'Check for optional worksheetname else use activesheet
    If WorksheetName = vbNullString Then 
        WorksheetName = ActiveSheet.Name 
    End If 
     
     '    find the last populated row in a worksheet
    With Worksheets(WorksheetName) 
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByRows, xlPrevious).Row 
    End With 
     
End Function 
--- End of place this In a module 
--- place the text between --- In the code For a userform 
Option Explicit 
Option Compare Text 
 
Private Sub UserForm_Activate() 
     
    Dim Cell As Range 'defining your searcharea
    Dim Row_Counter As Integer 'rowno
    Dim Pos As Integer 'rowno in array
    Dim MyList() As String 'the array
    Dim No_Pos As Integer 'total rows in aray
    Dim Current_pos As Integer 'position in sheet1
    Dim Search_name As String 'searchvalue in sheet1
    Dim Real_last_row As Integer 'last row in sheet2
     
     'xllastrow is a function
     
    Real_last_row = xlLastRow("Sheet2") 'last row in sheet2
    Current_pos = ActiveCell.Row 'after hitting enter
    Current_pos = Current_pos - 1 'before hitting enter
     'putting the value of the cell in a variable before you hitted enter
    Search_name = Worksheets("Sheet1").Range("A" & Current_pos) 
     
     'Searching in sheet2 for matches with searchstring
     'Needed for creating an array with unknown number of rows
     
    For Each Cell In Worksheets("Sheet2").Range("A2:A" & Real_last_row) 
        If Cell Like "*" & Search_name & "*" Then 
             'If a value matches with the searchstring, number of rows of array is + 1
            No_Pos = No_Pos + 1 
        End If 
    Next Cell 
     
    Row_Counter = 2 'start of the nameslist
    Pos = 0 'rowno in array, beginning with zero
    ReDim Preserve MyList(No_Pos, 3) 'Redimming of array with total no of rows and 3 columns
     
     'if value isn't in searchstring then rowno is rowno + 1
     'if a match is found the array is filled and the rowno in array is + 1
     
    For Each Cell In Worksheets("Sheet2").Range("A2:A" & Real_last_row) 
        If Cell Like "*" & Search_name & "*" Then 
            MyList(Pos, 0) = Worksheets("Sheet2").Range("A" & Row_Counter) 
            MyList(Pos, 1) = Worksheets("Sheet2").Range("B" & Row_Counter) 
            MyList(Pos, 2) = Worksheets("sheet2").Range("C" & Row_Counter) 
            Pos = Pos + 1 
            Row_Counter = Row_Counter + 1 
        Else 
            Row_Counter = Row_Counter + 1 
        End If 
    Next Cell 
     
    Application.ShowToolTips = True 
     
    With ListBox1 
        .ColumnCount = 3 'no of columns (0,1,2)
        .ColumnWidths = "3 cm;3 cm;3 cm" 'widths of columns
        .ControlTipText = "Possible matches ..." 'tiptext
        .ListStyle = fmListStylePlain 
        .SpecialEffect = fmSpecialEffectFlat 
    End With 
     
    ListBox1.List = MyList 'define the list of listbox1
     
End Sub 
--- End of place this In the userform_activate