Load Listbox only if two values are present in two cells otherwise ignore & continue down the sheet

[ipbr21054]

The working code in use is shown below.

It works like this.
On my worksheet in column D the values are sorted A-Z so when the listbox is populated its also shown A-Z
The userform opens & in Textbox1 is the value HONDA
The code looks down column C for HONDA & then collects the values from the columns D, I & L
These values are then placed into the listbox.
This works well.

BUT unfortuneatly last night i was advised something that was after this working code was supplied.

Ive been told we need to only populate the Listbox should another value on my sheet exist.
This value is in column L where the values will be either of the following.
CLEAR BLACK GREY RED

So the code should then work like this.
On my worksheet the code should still look in column C for the value HONDA but then look in column L for the value CLEAR BLACK GREY RED
If HONDA is present & either CLEAR BLACK GREY RED is present THEN continue to load Listbox.

Example
Column C shows HONDA & column L shows BLACK then continue TO LOAD LISTBOX WITH VALUES.

If column C shows HONDA & column L is EMPTY then ignore this & continue down the sheet

Private Sub CheckConnectorsUsed_Click()
  Dim r As Range, f As Range, Cell As String, added As Boolean
  Dim sh As Worksheet
  Dim i As Long
  Set sh = Sheets("MCLIST")
  sh.Select
  With ListBox1
    .Clear
    .ColumnCount = 4
    .ColumnWidths = "100;170;70;10"
    
    Set r = Range("C8", Range("C" & Rows.Count).End(xlUp))
    Set f = r.Find(TextBox1.Value, After:=r.Cells(r.Count), LookIn:=xlValues, LookAt:=xlPart)
    
    If Not f Is Nothing Then
      Cell = f.Address
      Do
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
        Set f = r.FindNext(f)
      Loop While f.Address <> Cell
      .TopIndex = 0
    End If
    End With

End Sub

 

[RoryA]

Is column L always either empty, or one of those 4 values, or could there be other values that should also be ignored?

 

[ipbr21054]

Morning,
Yes
Column L cells will either be empty or will have one of the values mentioned

 

[RoryA]

OK, then change the Do...Loop to this:

      Do
         If Len(Cells(f.row, "L").Value) <> 0 then
          .AddItem f.Value
          .List(.ListCount - 1, 1) = f.Offset(, 1).Value ' MODEL
          .List(.ListCount - 1, 2) = f.Offset(, 6).Value ' YEAR
          .List(.ListCount - 1, 3) = f.Offset(, 9).Value ' CONNECTOR USED
          .List(.ListCount - 1, 4) = f.Row
         end if
        Set f = r.FindNext(f)
      Loop While f.Address <> Cell

 

[ipbr21054]

Thanks that worked.

Just a question whilst im on the same form.
If i weere to add say 4 Textboxes would i be able to count each occurance & enter in a Textbox.
Example TextBox2 would be labelled CLEAR but the value shown would be the how many occurancies of CLEAR were used.
Would the code look at worksheet or Listbox entry ?

 

[RoryA]

The code could look at either, but it probably makes sense to count them as you populate the listbox.

 

[ipbr21054]

Ok will see if i can work it out or will post a new thread.