Listbox populate based on checkbox selection?

[emichan]

Hi all,

I'm trying to create a userform with a listbox that populate from a dynamic range.

Private Sub UserForm_Initialize()


    With Listbox1
        .RowSource = Range(Sheets("validation").Range("c2"), Sheets("validation").Range("c2").End(xlDown)).Address(, , , True)
        .ColumnCount = 1
        .MultiSelect = fmMultiSelectExtended
        
    
   End With


End Sub

let's say that the range in validation!C2 has a list of tasks. In column D, I have initials of the person responsible for said task.

I would like to filter the listbox rows using checkbox.

So, if there are 4 people, I have 4 checkboxes with their initials. Their initials, as mentioned, is listed in column D next to the list of tasks.

So, if "JS" for John Smith is checked, I want to show in the listbox only the tasks assigned to him.



Basically, assuming I want the listbox to populate for "John Smith", I want the listbox source to show the equivalent of

=iferror(index(c2:c52,match(small(if(d2:d52="JS",e2:e52)),row()-2),e2:e52,0),1),"")

Where "JS" is the initials, C2:C52 is the list of all tasks, D2:D52 is the list of initials, and e2:e52 has =ROW() so I can run a =SMALL(range,k) to get list of all tasks assigned to JS, and the formula is entered in a cell in row 3, hence the nth smallest is row()-2.

(I made the above ranges static for simplicity).


I hope this is clear.

Thank you!

 

[Keebellah]

Hi, I haven't done much on this forum as yet.
I've read your question but it left me with quite some ? ? ?
Could you attach a sample file (dummy / non-private data) and show it there?
A pictures does (many times) tell more than a thousand words.

 

[emichan]

I'm sorry! I looked at how to attach workbooks and it looks like I can't unless I get an add-in. I don't have admin rights so I can't install anything on my work pc..

However, I'll post all the vba for my dummy workbook.

Private Sub CommandButton1_Click()

'Submit button

Dim i As Long


Set ws = ThisWorkbook.Sheets("validation")


    i = ListBox2.ListCount
    ws.Range(ws.Range("m1"), ws.Cells(i, "m")).Value = ListBox2.List
    
    UserForm1.Hide




End Sub


Private Sub CommandButton2_Click()
'add from listbox1 to listbox2

Dim i As Long


    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.Selected(i) = True Then
            ListBox2.AddItem (ListBox1.List(i))
        End If
        
    Next i
        


End Sub


Private Sub UserForm_Initialize()
'listbox populate from sheet validation

With ListBox1


    .RowSource = Range(Sheets("validation").Range("c2"), Sheets("validation").Range("c2").End(xlDown)).Address(, , , True)
    .ColumnCount = 1
    .MultiSelect = fmMultiSelectExtended
End With


    


End Sub

So, in validation!C2:C52, I have the list of tasks. In D2:D52, I have the initials of the people responsible for the tasks.
When I click "submit" (commandbutton1), it will spit out the list of tasks that i selected to validation!m1.

I would like the listbox1 to show the list of all tasks based on checkboxes for each initial. So, if checkbox1 ("JS") is true, show only the tasks in column C which has JS in column D.

I hope this is clear! I apologize for the confusion!

 

[Keebellah]

The code doesn't really tell me much without the data and I learned the hard way that assumptions don't work with VBA and coding
I cannot attach files either and in my case it's because I haven't posted much here. It's not an Addin you require it's just an active link.

 

[My Aswer Is This]

This script should load the listbox with the values you want.
Assuming your sheet is name: Validation
And the script will look in all the check boxes on your userform and look to see if the name of the checkbox is in column D

And if so will load the value in column C into the listbox

It will also check to be sure you have only one checkbox checked.

Private Sub CommandButton2_Click()
'Modified 6/25/18 7:40 PM EDT
Dim ckcount As Long
ckcount = 0
Dim ctrl As MSForms.Control
 Dim ans As String
 ListBox1.Clear
For Each ctrl In Me.Controls
    Select Case True
        Case TypeOf ctrl Is MSForms.CheckBox
            If ctrl.Value = True Then ans = ctrl.Name: ckcount = ckcount + 1
    End Select
    
Next ctrl
If ckcount > 1 Then MsgBox "You have more then one Checkbox selected" & vbNewLine & "I will now end this script": Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Validation").Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Validation").Cells(i, "D").Value = ans Then
ListBox1.AddItem Sheets("Validation").Cells(i, "C").Value
End If
Next
End Sub

 

[emichan]

Thank you for your help!

Apologies for the late response, but I am trying your suggestion now.


However, I'm unfortunately getting an "Unspecified error" for listbox1.clear...

Would you be able to advise?

 

[Keebellah]

It doesn't 'recognize' Listbox1 at that point in the code

 

[My Aswer Is This]

Thank you for your help!

Apologies for the late response, but I am trying your suggestion now.


However, I'm unfortunately getting an "Unspecified error" for listbox1.clear...

Would you be able to advise?

It's telling you you do not have a listbox named listbox1.

This is a default name of a listbox.

What is the name of the listbox you want to us?

 

[emichan]

Sorry, figured it out! it's because I had the listbox1 populating with .rowsource when userbox is initialized.
With some modification, I have the below now working.

Thank you!

Private Sub Filter_Click()


    Dim ctrl As Control
    Dim ckcount As Long, lastrow As Long, i As Long
    Dim ans As String
    
    
    ckcount = 0
    For Each ctrl In Me.Controls
        Select Case True
            Case TypeName(ctrl) = "CheckBox"
                If ctrl.Value = True Then
                    ans = ctrl.Caption
                    ckcount = ckcount + 1
                End If
        End Select
    Next ctrl
    
    lastrow = Sheets("Validation").Cells(Rows.Count, "c").End(xlUp).Row
    
    
    
    If ckcount = 0 Then
        With Listbox1
            .RowSource = Range(Sheets("validation").Range("C2"), Sheets("validation").Range("c2").End(xlDown)).Address(, , , True)
            .ColumnCount = 1
            .MultiSelect = fmMultiSelectExtended
        End With
    End If
        
    If ckcount > 0 Then
        Listbox1.RowSource = vbNullString
        
        For i = 2 To lastrow
            If Sheets("validation").Cells(i, "d").Value = ans Then
                With Listbox1
                    .AddItem (Sheets("validation").Cells(i, "C").Value)
                    .MultiSelect = fmMultiSelectExtended
                    
                End With
            End If
        Next
    End If
End Sub

 

[My Aswer Is This]

If your happy we are happy.
Not sure why you insisted on using RowSource
But if it works for you that's all that is important.

Not sure why you did not like my script.

Using rowsource can cause you problems in some situations like trying to remove values from a listbox.