Will not show line description

[josros60]

Hi,

Have the code below works find is just when double click line description is blank (see pictures line description in blank highlighted in yellow).

Code:

Private Sub Search_PRIMUS_Click()
Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer
    
    ' Display an error if no search term is entered
    If SAGEID.Value = "" And VENDOR.Value = "" And APPROVER.Value = "" And GL_CODE.Value = "" Then
    
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    
    End If
    
    ' Work out what is being searched for
    If SAGEID.Value <> "" Then
    
        SearchTerm = SAGEID.Value
        SearchColumn = "SAGEID"
        
    End If
    
    If VENDOR.Value <> "" Then
    
        SearchTerm = VENDOR.Value
        SearchColumn = "VENDOR"
        
    End If

    If APPROVER.Value <> "" Then
    
        SearchTerm = APPROVER.Value
        SearchColumn = "APPROVER"
        
    End If

    If GL_CODE.Value <> "" Then
    
        SearchTerm = GL_CODE.Value
        SearchColumn = "ACCOUNT"
        
       
    
    End If
   Results2.Clear
    
        ' Only search in the relevant table column i.e. if somone is searching Location
        ' only search in the Location column
        With Range("Table1[" & SearchColumn & "]")
        
        ' Find the first match
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

            ' If a match has been found
            If Not RecordRange Is Nothing Then

                FirstAddress = RecordRange.Address
                RowCount = 0

                Do
                
                    ' Set the first cell in the row of the matching value
                    Set FirstCell = Range("A" & RecordRange.Row)
                    
                    ' Add matching record to List Box
                   Results2.AddItem
                   Results2.List(RowCount, 0) = FirstCell(1, 1)
                   Results2.List(RowCount, 1) = FirstCell(1, 2)
                   Results2.List(RowCount, 2) = FirstCell(1, 3)
                   Results2.List(RowCount, 3) = FirstCell(1, 4)
                   Results2.List(RowCount, 4) = FirstCell(1, 5)
                   Results2.List(RowCount, 5) = FirstCell(1, 6)
                   Results2.List(RowCount, 6) = FirstCell(1, 7)
                   Results2.List(RowCount, 7) = FirstCell(1, 8)
                   Results2.List(RowCount, 8) = FirstCell(1, 9)
                    RowCount = RowCount + 1
                    
                    ' Look for next match
                    Set RecordRange = .FindNext(RecordRange)

                    ' When no further matches are found, exit the sub
                    If RecordRange Is Nothing Then

                        Exit Sub

                    End If

                ' Keep looking while unique matches are found
                Loop While RecordRange.Address <> FirstAddress

            Else
            
                ' If you get here, no matches were found
               Results2.AddItem
               Results2.List(RowCount, 0) = "Nothing Found"
            
            End If
          
            

        End With

End Sub

Thank you,

 

[DanteAmor]

when double click line description

You forgot to put here the doubleclick event code

 

[josros60]

Sorry, Here the double click event"

Private Sub Results2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim UF As Object
    
    Set UF = UserForm1
    
    With Me.Results2
    
      
       
      UF.cmbslno.Value = .List(.ListIndex, 0)
      UF.txtvendor.Value = .List(.ListIndex, 1)
      UF.txtaccount.Value = .List(.ListIndex, 2)
      UF.txtlinedescription.Value = .List(.ListIndex, 9)
      UF.txtglcode.Value = .List(.ListIndex, 8)
      UF.txtgldescription.Value = .List(.ListIndex, 5)
      UF.txtpercentage.Value = .List(.ListIndex, 4)
      UF.txttaxes.Value = .List(.ListIndex, 7)
      UF.txtapprover.Value = .List(.ListIndex, 3)
      UF.txtcurrency.Value = .List(.ListIndex, 6)
      '(...)
      '======================================================
      
    End With
        
    UF.Show
    
    'Unload Me
   
End Sub

Thank you,

 

[RoryA]

Your code never populates that column.

                   Results2.AddItem
                   Results2.List(RowCount, 0) = FirstCell(1, 1)
                   Results2.List(RowCount, 1) = FirstCell(1, 2)
                   Results2.List(RowCount, 2) = FirstCell(1, 3)
                   Results2.List(RowCount, 3) = FirstCell(1, 4)
                   Results2.List(RowCount, 4) = FirstCell(1, 5)
                   Results2.List(RowCount, 5) = FirstCell(1, 6)
                   Results2.List(RowCount, 6) = FirstCell(1, 7)
                   Results2.List(RowCount, 7) = FirstCell(1, 8)
                   Results2.List(RowCount, 8) = FirstCell(1, 9)
                    RowCount = RowCount + 1

Note there is no line to populate Results2.List(RowCount, 9)

 

[DanteAmor]

Sorry, Here the double click event"

'(...)

What do those three dots mean?
You have more code, so you need to add all your code. That part you added doesn't pass any values to the sheet; you only pass values from the list box to other controls in a user form.

 

[josros60]

Thank you so much, worked perfectly.