Making Listbox selection to also clear Textbox values that dont apply

ipbr21054

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


VBA Code:
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.






EaseUS_2025_02_28_10_16_36.jpg
 
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.


Rich (BB code):
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
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
VBA Code:
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
 
Upvote 0
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

EaseUS_2025_02_28_17_00_41.jpg
 
Upvote 0
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.
 
Last edited:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top