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]

Any advice please

 

[hungtbatman1]

Any advice please

Attach Excel file

 

[hungtbatman1]

I see now that you are asking in a different thread.

 

[ipbr21054]

Where ???

 

[hungtbatman1]

Thread "Help me understand making listbox selection failed to select on worksheet"

 

[ipbr21054]

Two different forms in question

 

[ipbr21054]

Here is the file to test.

On the DATABASE tab at the top select DECENT SEARCH.

In the first combobox under VEHICLE make a selection.

Now click the result in the list box where now it should take me to customer on sheet but i actually get a RTE 1004



DOWNLOAD FILE

 

[ipbr21054]

Here is the other issue which you said i had posted same thing twice BUT i hadnt

As you can see BOTH are separate issues & not the same

 

[hungtbatman1]

ListBox1.List(ListBox1.ListIndex, 0) returns STRING (person) and not integer, so

Range("A" & ListBox1.List(ListBox1.ListIndex, 0)).Select

definitely causes an error.
You don't have enough data to test Sub FilterData

Are the people in column A unique? Or in other words: does any person in column A appear >= 2 times?
If each person appears only once, you can use FIND with ListBox1.List(ListBox1.ListIndex, 0)
If the data in column A is not unique, you need to add some code