Filter Listbox In Form

[TellM1955]

Hi all, once again I'm looking for some assistance. I've a created a form to add Students with daily defaults however, when a student is absent then I plan to will mark him as such. However, on the following day I need t to have a means of identifying those who are absent and change their status if necessary. To achieve this, I'm looking to filter those students who are identified as being absent to enable editing their records. I've attached a sample file to assist.

Thanks

OneDrive

 

[6StringJazzer]

You are currently populating the list by loading all students from Sheet students using RowSource. You have to use a different method to filter it. In the Sub AddDataToListBox,
you will have to have a loop like this:

   Dim Cell As Range
   Dim Col As Long
 
   With LstStudents
     
        For Each Cell In rg.Columns(1)

        .ColumnCount = rg.Columns.Count
        .ColumnWidths = "0;60;60;30;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"

           If <this student is to be included> Then

              .AddItem Cell
              For Col = 2 To 29
                  .List(.ListCount - 1, Col - 1) = rg.Cells(.LstCount, Col)
              Next Col

         End If
    Next Cell
       
    
     End With

PENDING TESTING

This will also cause you to lose your column titles, and you will have to put the titles in a label on the UserForm above the listbox.

You didn't include enough information to explain how students are marked absent and how to use that for a particular day to exclude the student. So I can't provide the actual code to do the filtering. By the way, it would help to understand a little more about what you're doing. I am not clear on why, if a student is absent one day, you omit him from the list the following day.

 

[6StringJazzer]

New and improved

Private Sub AddDataToListBox()


    'Get the Range
    Dim rg As range
    Set rg = GetRange()
   
    'Link the data to the list box
    
   Dim Cell As range
   Dim Col As Long
  
   With LstStudents
  
      .ColumnCount = rg.Columns.Count
      .ColumnWidths = "0;60;60;30;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0"
     
      For Each Cell In rg.Columns(1).Cells

        
         If True Then ''''''''''''''''' your filter condition goes here
        
            .AddItem Cell
            For Col = 2 To 29
               .List(.ListCount - 1, Col - 1) = rg.Cells(.ListCount, Col).Value2
            Next Col
        
         End If
        
      Next Cell
  
  
   End With
    
txtFName.SetFocus
End Sub

 

[TellM1955]

Many thanks for looking into this. I don't know what happened but it appears the wrong file was uploaded, this has been corrected and hopefully you can see how a student is marked absent. I've had a cursory look and changed out te code for AddDataToListBox1 but now get an error, to which a you'll probably surmise I have no idea as to why and what to do!
Cheers