Make selection in Listbox & be taken to that customer on worksheet

[ipbr21054]

I have a user form with 3 comboboxes that populate the Listbox.
I am trying that when a user makes a selection in the listbox that he is taken to that customer on the worksheet & the userform closes.
The customer on the worksheet is in column A

I have added the code shown in red below but it only closes the form & doesnt take the user to his selection.
I get no RTE messages

Please advise thanks

Dim a As Variant

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, cmb3 As String
  Dim i As Long, j As Long, k As Long, n As Long
  Dim b As Variant
  
  ListBox1.Clear
  n = WorksheetFunction.CountIfs(Range("D:D"), ComboBox1.Value & "*", _
        Range("F:F"), ComboBox2.Value & "*", Range("G:G"), ComboBox3.Value & "*")
  If n = 0 Then
    MsgBox "THERE ARE NO RECORDS TO DISPLAY", vbCritical, "NO DISPLAY RECORDS MESSAGE"
    Exit Sub
  End If
  ReDim b(1 To n, 1 To 7)
  
  For i = 1 To UBound(a, 1)
    If ComboBox1.Value = "" Then cmb1 = a(i, 4) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 6) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 4) = cmb1 And a(i, 6) = cmb2 And a(i, 7) = cmb3 Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  ListBox1.List = b
End Sub
Private Sub CloseForm_Click()
Unload DecentSearch
End Sub
Private Sub ComboBox1_Change()
  ComboBox2.Value = ""
  ComboBox3.Value = ""
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  ComboBox3.Value = ""
  Call FilterData
End Sub
Private Sub ComboBox3_Change()
  Call FilterData
End Sub
Private Sub ListBox1_Click()
Range("A" & ListBox1.List(ListBox1.ListIndex, 4)).Select
Unload DecentSearch
End Sub

Private Sub UserForm_Activate()
  Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim i As Long
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  a = Range("A1:G" & Range("D" & Rows.Count).End(3).Row).Value
  ListBox1.ColumnCount = 7
  
'first column D second column F & third column G
  For i = 2 To UBound(a, 1)
    dic1(Range("D" & i).Value) = Empty
    dic2(Range("F" & i).Value) = Empty
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.Keys
  ComboBox2.List = dic2.Keys
  ComboBox3.List = dic3.Keys
  
  With Me.ListBox1
   .ColumnWidths = "190;100;140;140;100;150;150"
   .Width = 975
  End With

  Dim rngData As Range
  
  Set rngData = Worksheets("DATABASE").Range("D6:D1000")
      Me.ComboBox1.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
        
  Set rngData = Worksheets("DATABASE").Range("F6:F1000")
      Me.ComboBox2.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")

  Set rngData = Worksheets("DATABASE").Range("G6:G1000")
      Me.ComboBox3.List = Evaluate("SORT(DATABASE!" & rngData.Address & ")")
      
  End Sub

 

[ipbr21054]

I just do the job & customer is then added to my worksheet.
Like so TOM JONES 001

If same customer asks for another job then when added to worksheet its like so TOM JONES 002 so i would say all unique

 

[hungtbatman1]

so i would say all unique

Form DecentSearch

Private Sub ListBox1_Click()
Dim rng As Range
    Set rng = Worksheets("DATABASE").Range("A1:A" & UBound(a, 1)).Find(ListBox1.List(ListBox1.ListIndex, 0), , xlValues, xlWhole, xlColumns, xlNext)
    If Not rng Is Nothing Then rng.Select
    Unload DecentSearch
End Sub

 

[ipbr21054]

Tried it on 5 customers & all work.

Perfect many thanks