Edit current working Userform advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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.


Rich (BB code):
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
I suppose i should write please advise how i can populate a listbox using an array
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,892
Members
412,752
Latest member
LUIS SAMANO
Top