Could not get the Column Property in ListBox_DoubleClick function

[2020Rivalry]

The screenshot for my userform:

I implemented the code to filter using txtSearch.value (the textbox below Search by SAP Code) just now:

Private Sub txtSearch_Change()

    Dim myList As Variant, Rws As Variant
    Dim FoundSomething As Boolean
    With Sheets("Sheet1")
      With .Range("A6:N" & .Range("A" & Rows.Count).End(xlUp).Row)
         Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.txtSearch.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)
         If UBound(Rws) < 0 Then
            FoundSomething = True
         ElseIf UBound(Rws) = 0 Then
            myList = .Parent.Range("A" & Rws(0) + .Row - 1).Resize(, 14).Value
         Else
            myList = Application.Index(.Value, Application.Transpose(Rws), [sequence(,14)])
         End If
      End With
   End With
    If Not FoundSomething Then
    ListBox.RowSource = ""
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        ListBox.List = myList
    Else
        Call Refresh_data
    End If

End Sub

However, new problem appear as my previous code included listbox_double click activity, meaning after I click the filtered data, my other textboxes will track the specific filtered data:
VBA Code:

Private Sub ListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   
   
txtSearch.Text = Me.ListBox.Column(0)
txtSearch1.Text = Me.ListBox.Column(2)
txtSAPCode.Text = Me.ListBox.Column(0)
txtSAPDescription.Text = Me.ListBox.Column(1)
txtBookingNo.Text = Me.ListBox.Column(2)
txtShipmentNo.Text = Me.ListBox.Column(3)
txtPONo.Text = Me.ListBox.Column(4)
txtDateFrom.Text = Me.ListBox.Column(5)
txtDateTo.Text = Me.ListBox.Column(6)
txtQuantity.Text = Me.ListBox.Column(7)
txtManufacturingDate.Text = Me.ListBox.Column(8)
txtCapsuleBatch1.Text = Me.ListBox.Column(9)
txtCapsuleBatch2.Text = Me.ListBox.Column(10)
txtDateofDespatch.Text = Me.ListBox.Column(11)
txtTrackSubmittedBy.Text = Me.ListBox.Column(12)
txtTrackSubmittedOn.Text = Me.ListBox.Column(13)
   
End Sub

But now after I double click the specific filtered data, error occur:

And seems like code cannot capture the column heads:

Really appreciate some help, thanks!

 

[Jon Peltier]

A ListBox does not have columns and rows as such. That's why this command will fail:

txtSearch.Text = Me.ListBox.Column(0)

You need to specify the "row" and "column" as part of the List property of the ListBox:

txtSearch.Text = Me.ListBox.List(0, 0)

You probably want the index of the selected row, so:

Dim SelectedRow As Long
SelectedRow = Me.ListBox.ListIndex
txtSearch.Text = Me.ListBox.List(SelectedRow, 0)

 

[2020Rivalry]

Thanks Jon! It does solve the error.

But why the header didn't show up in the listbox once I run the txtSearch_Change? Like this:

 

[Jon Peltier]

I don't see the listbox, and I don't see where you're trying to capture the headers.

I never use RowSource, but it seems to me that if ListBox.RowSource = "" then there are no headers, since there is no range.

 

[2020Rivalry]

I capture them from sheet 1: (row 5 is header and the data start from row 6)

Private Sub Refresh_data()
   
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim le As Long
    lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
   
    If lr = 5 Then lr = 6
   
    With Me.ListBox
        .ColumnCount = 14
        .ColumnHeads = True
        .ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
        .RowSource = "Sheet1!A6:N" & lr
    End With
       
End Sub

 

[Jon Peltier]

Okay, you probably want to use 0, not .ListIndex, assuming that's how the headers are stored in the listbox (as I said, I never use row source).

 

[2020Rivalry]

You mean

txtSearch.Text = Me.ListBox.List(0, 0)

instead of putting ListIndex right?

But how I gonna specify the "row" and "column" as part of the List property of the ListBox since they are dynamic.

 

[Jon Peltier]

You wanted the header, right? That's in row 0 (I presume). If you want a different row, you need to calculate or somehow. Same with the column.

 

[2020Rivalry]

Thank you very much!