Populate Listbox based on another listbox and criteria

[jacob1234]

Hi All,

I'm stuck with the answer on this one,

I have a userform with 2 ListBoxes, Listbox1 is filled with 9 unique values. These 9 values are school programs. I have the user form submitting data on whether students are enrolled in any of these classes. It posts a TRUE of FALSE in one of these columns.

What I need is for the user to select a school program in Listbox 1 and in Listbox 2 it returns the students n are if the value in the school program column = TRUE.

Problem is, these 9 school programs and the TRUE/FALSE values are spread out into col E,H,K,N,Q,T,W,Z,AC, while the Students first and last name is in col A and B

Does anyone know a way to return the students name if the specific school program is selected and the value = TRUE?

Help would be appreciated!!

Thanks!

 

[Norie]

Do columns E, H, K etc. represent the programs?

If they do are there headers that correspond to the unique values in the first listbox?

 

[jacob1234]

Hi Norie,

Yes those columns represent the programs and they do have unique headers that correspond to the values in the first list box

Thanks for your help!

 

[Norie]

What you could do then is use Application.Match to find the column that corresponds to the program selected in the first listbox then go down that column and whenever you find a row with TRUE in it you add the values from column A and B of that row to the second listbox.

Something like this where I've assumed the data is on a sheet called 'Students' and the header is in row 1.

Private Sub lstProgram_Change()
Dim Res As Variant
Dim I As Long

    If lstProgram.ListIndex <> -1 Then

        Res = Application.Match(lstProgram.Value, Sheets("Students").Rows(1), 0)

        If Not IsError(Res) Then
             ' populate 2nd listbox

             With Sheets("Students")
                 For I = 2 To .Cells(Rows.Count, Res).End(xlUp).Row
                     If .Cells(I, Res).Value = "TRUE" Then 
                         lstAttendees.AddItem .Cells(I, "A").Value & .Cells(I, "B").Value
                     End If
                 Next I
             End With

        End If

   End If

End Sub

 

[jacob1234]

Hi Norie thanks for your help! I have added in the code using my variables below but it doesn't fill Listbox2 with any values?

Private Sub ListBox3_Click()

Dim Res As Variant
Dim I As Long


    If ListBox1.ListIndex <> -1 Then


        Res = Application.Match(ListBox1.Value, Sheets("Database").Rows(1), 0)


        If Not IsError(Res) Then
             ' populate 2nd listbox


             With Sheets("Database")
                 For I = 2 To .Cells(Rows.Count, Res).End(xlUp).Row
                     If .Cells(I, Res).Value = "TRUE" Then
                         ListBox2.AddItem .Cells(I, "A").Value & .Cells(I, "B").Value
                     End If
                 Next I
             End With


        End If


   End If


End Sub

 

[Norie]

The code should go in the Change event of the combobox that lists the programs.

 

[jacob1234]

Hi Norie, thanks again,

I populate using a listbox not a combobox, but I have fixed the listboxs now, and still nothing?

Private Sub Listbox3_Change()Dim Res As Variant
Dim I As Long


    If ListBox3.ListIndex <> -1 Then


        Res = Application.Match(ListBox3.Value, Sheets("Database").Rows(1), 0)


        If Not IsError(Res) Then
             ' populate 2nd listbox


             With Sheets("Database")
                 For I = 2 To .Cells(Rows.Count, Res).End(xlUp).Row
                     If .Cells(I, Res).Value = "TRUE" Then
                         ListBox4.AddItem .Cells(I, "A").Value & .Cells(I, "B").Value
                     End If
                 Next I
             End With


        End If


   End If


End Sub

 

[jacob1234]

Hey Norie! - Please disregard I found the error it wasn't picking up names because of the apostrophes around "TRUE"

Thanks!