Making Listbox selection to also clear Textbox values that dont apply

[ipbr21054]

Code shown below in use & works well BUT i wish to slightly expand on it.

Currently this works fine,
When a Listbox selection is made the colour selected is shown only & the others are cleared

This is the part i would like to also be added,
Upon making a colour selection clear the Textbox values mentioned that are not that selected colour & to only show that colours count value.

Basically if the Value selected is RED then i only need to see the value count for RED in its Textbox & make others 0
Same applies for another the colours when selected.

Currently when used im seeing values for all colours, see SCREEN SHOT EXAMPLE

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
   
    colour = ListBox1.List(ListBox1.ListIndex, 3)
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
   
End Sub

Thanks to @NoSparks for the original code supplied above.

Please advise correct code Thanks.

 

[ipbr21054]

Some extra information for you.
Currently my userform Textboxes are being populate as shown in my Initialize code below.

Maybe an option NOT to populate Texbox values that way & to use another counting code so the Textbox values update themselves going by what colours are shown in the Listbox at the time in question.

Private Sub UserForm_Initialize()
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 15 ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 330 ' LEFT / RIGHT OF SCREEN HIGHER NUMBER MOVES FORM TO THE LEFT
    
    TextBox1.Visible = False
    TextBox1.Value = "HONDA"
    
    
  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
         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
      .TopIndex = 0
    End If
    End With
    
    TextBox2.Text = ActiveSheet.Range("L1").Value ' BLACK
    TextBox3.Text = ActiveSheet.Range("L2").Value ' CLEAR
    TextBox4.Text = ActiveSheet.Range("L3").Value ' GREY
    TextBox5.Text = ActiveSheet.Range("L4").Value ' RED

End Sub

 

[NoSparks]

Don't know why you need 4 textboxes for this when at any time 3 of them are to be 0.
Personally I'd use a single textbox or more likely a label if just a simple message box wouldn't do.
any way...

You haven't given us the textbox names that the color counts are to go into.
I've just used the textboxes from your code in red. You'll need to change this to suit what you actually have.

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
    
    ' filter listbox on 4th column
    colour = ListBox1.List(ListBox1.ListIndex, 3)
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
           
    ' how many of this color
    ' Me.count_label.Caption = ListBox1.ListCount & "  " & colour
    
    ' clear the count textboxes
    For i = 2 To 5
        Controls("TextBox" & i) = 0
    Next i
    
    Select Case colour
        Case "BLACK"
            Me.TextBox2 = ListBox1.ListCount
        Case "CLEAR"
            Me.TextBox3 = ListBox1.ListCount
        Case "GREY"
            Me.TextBox4 = ListBox1.ListCount
        Case "RED"
            Me.TextBox5 = ListBox1.ListCount
    End Select
        
End Sub

 

[ipbr21054]

4 Textboxes were in use before you advised your code hence why now looking to do something about it.
I also left ut there as an example of what’s to happen / be shown.
Will look once back

 

[ipbr21054]

Ok so that worked & i see your point with me not advising where value was to be shown.

Lets assume its then TextBox7 then please

 

[ipbr21054]

@NoSparks Dont advise yet as just come across something & need to sort that first>

Thanks

 

[ipbr21054]

Ok so now we can continue where we left off.
Which was having 1 x Textbox7 to show the colour count as opposed to numberour Textboxes where 3 would be showing 0


Thanks.
Need to pop out for 2 hours

 

[NoSparks]

Private Sub ListBox1_Click()
    Dim i As Long
    Dim colour As String
    
    ' filter listbox on 4th column
    colour = ListBox1.List(ListBox1.ListIndex, 3)
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.List(i, 3) <> colour Then
            ListBox1.RemoveItem (i)
        End If
    Next i
           
    ' how many of this color
     Me.TextBox7 = ListBox1.ListCount & "  " & colour
End Sub

 

[ipbr21054]

Weve lost the sort feature of A-in the Model row.
See post #1 for what it looked like to now

Might be trivial but ive got OCD

 

[NoSparks]

Weve lost the sort feature of A-in the Model row.

No we haven't, because there has never been any sorting applied in the user form.
It's your data that has changed.
The listbox is populated in order as things appear on your MCLIST sheet.

Your original post back here in the title it says sort but everything there after (and in threads since) is akin to filtering.