Run time erorr 94 when selecting value in Listbox

[ipbr21054]

I have a userform with various search options that place the results in the Listbox
Each search option works fine searching & when i click the result in the Listbox, apart from one of them.

This being the search option KEY TYPE.
I enter KW 16 in the search field & i see the 2 results in the Listbox.
When i click the value in the Listbox i then see the error message.
I debug & see this line in yellow.

Private Sub ListBox1_Click()
     Dim rw As Long
     Dim ws As Worksheet
     Dim answer As Integer
     Set ws = ThisWorkbook.Sheets("Database")
    If ListBox1.ListIndex = -1 Then Exit Sub
     rw = ListBox1.List(ListBox1.ListIndex, 3)
     ws.Range("A" & rw).Select
     Unload Me
     
   answer = MsgBox("OPEN CUSTOMERS FILE IN MAIN DATABASE ?", vbYesNo + vbInformation, "OPEN DATABASE MESSAGE")
   If answer = vbYes Then
   
   Database.LoadData Sheets("DATABASE"), rw

    Else
    Unload DatabaseSearchForm
    End If
  End Sub

 

[jkpieterse]

Apparently column 3 of the list of the listbox is empty for that item? Please show the code that populates the listbox.

 

[ipbr21054]

I believe this is what you need

Private Sub TextBoxKeyType_Change()
Me.Label1.Visible = False
Me.Label2.Visible = False
Me.Label3.Visible = False
Me.Label4.Visible = False
TextBoxKeyType = UCase(TextBoxKeyType)
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
 
  Set sh = Sheets("DATABASE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "200;180;260;10"
   If TextBoxKeyType.Value = "" Then Exit Sub
    Set r = Range("C5", Range("C" & Rows.count).End(xlUp))
    Set f = r.Find(TextBoxKeyType.Value, LookIn:=xlValues, LookAt:=xlPart)
    If Not f Is Nothing Then
      Cell = f.Address
      Do
        added = False
        For i = 0 To .ListCount - 1
          Select Case StrComp(.List(i), f.Value, vbTextCompare)
            Case 0, 1
              .AddItem f.Value, i
              .List(i, 1) = f.Offset(, -2).Value
              .List(i, 2) = f.Offset(, -1).Value
              .List(i, 4) = f.Row
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, -2).Value
          .List(.ListCount - 1, 2) = f.Offset(, -1).Value
          .List(.ListCount - 1, 4) = f.Row
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      TextBoxSearch = UCase(TextBoxSearch)
      .TopIndex = 0
    Else
      MsgBox "NO SUCH KEY TYPE FOUND", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
      TextBoxKeyType.Value = ""
      TextBoxKeyType.SetFocus
    End If
  End With
End Sub

 

[jkpieterse]

Looks like column 3 of the listbox is never filled (there is no ".List(i, 3) =" line in your code)?

 

[ipbr21054]

This 4 should be a 3 Thanks

.List(i, 4) = f.Row