Edit current working Userform advice

[ipbr21054]

Morning,
I have a userform which consists of ComboBo1 & ListBox1
In ComboBox are the selectable options of the following,
COLLECTION, LOST, RECEIVED NO DATE, RETURNED, UNKNOWN
Once an option has been selected i see in the ListBox the results in the form of Choosen Selectable Option, Customers Name, Date, Row Number.
Example.
LOST TOM JONES 22/05/2020 186

What i am looking for is that when the userform is opened the code should run itself without me having to select any option to look for
from the ComboBox & put results in ListBox like so.

COLLECTION PAUL BROWN 16/08/2019 123
COLLECTION IAN SMITH 01/01/2020 345
LOST BRIAN SMITH 22/06/2020 678
NO DATE STEVE BRYANS 05/12/2020 122
RETURNED IVOR ALAN 22/04/2016 358
UNKNOWN ABBY REECE 18/05/2020 466

Some info for you.

Option Name are in column E
Customers names are in column B
Date is in column G
Row number
Selecting a result would then take me to that on the worksheet.

The code in use basically does what i require but its coded that you need to select an option
Might be of help to you to edit / save time etc so ive supplied it below.

Private Sub ListBox1_Click()
  Range("E" & ListBox1.List(ListBox1.ListIndex, 3)).Select
  Unload PostalIssueForm
End Sub

Private Sub ComboBox1_Change()
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  
  Set sh = Sheets("POSTAGE")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "150;220;90;10"
    If ComboBox1.Value = "" Then Exit Sub
    Set r = Range("G8", Range("G" & Rows.Count).End(xlUp))
    Set f = r.Find(ComboBox1.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                 'DATE RECEIVED
              .List(i, 1) = f.Offset(, -5).Value  'NAME
              .List(i, 2) = f.Offset(, -6).Value  'DATE
              .List(i, 3) = f.Row                 'ROW
              added = True
              Exit For
          End Select
        Next
        If added = False Then
          .AddItem f.Value                                 'DATE RECEIVED
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 3) = f.Row                 'ROW
        End If
        Set f = r.FindNext(f)
      Loop While Not f Is Nothing And f.Address <> Cell
      ComboBox1 = UCase(ComboBox1)
      .TopIndex = 0
    Else
      MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
      ComboBox1.Value = ""
      ComboBox1.SetFocus
    End If
  End With
End Sub

 

[ipbr21054]

I have progressed a little bit still need some help.
move managed to type the word into the text box which then the code looks in the column and puts the results in the Listbox. Clicking on the name the selects the item on the worksheet.

my goal is to not select or type anything but just look for the key words and put in listbox.
Key words shown in first post.

 

[ipbr21054]

Plodding on slowly but still not getting to where i need to be.

So the code below searches Column G for the Specific words i need to be found.
This is good only in the respect of it does it well.

Downside is it colours the cells RED & im looking for a way to not colour cells RED but to load into ListBox1 somehow

Any help is welcomed
Thanks

Private Sub CommandButton2_Click()
    Dim MyAr(1 To 5) As String
    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("POSTAGE")

    MyAr(1) = "COLLECTION"
    MyAr(2) = "LOST"
    MyAr(3) = "NO DATE"
    MyAr(4) = "RETURNED"
    MyAr(5) = "UNKNOWN"
     

    With ws
        For i = LBound(MyAr) To UBound(MyAr)
            Set aCell = .Columns(7).Find(What:=MyAr(i), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

            If Not aCell Is Nothing Then
                Set bCell = aCell
                aCell.Interior.ColorIndex = 3

                Do
                    Set aCell = .Columns(7).FindNext(After:=aCell)

                    If Not aCell Is Nothing Then
                        If aCell.Address = bCell.Address Then Exit Do
                        aCell.Interior.ColorIndex = 3
                    Else
                        Exit Do
                    End If
                Loop
            End If
        Next
    End With
End Sub

 

[ipbr21054]

I suppose i should write please advise how i can populate a listbox using an array