vendredi 5 janvier 2018

How to filter a multicolumn listbox with Textbox and Checkbox (Excel vba)

I have designed a simple planning system that processes data in a list box. I need to filter the projects with a textbox ("TbxZoekProjecten") that you will find in the left corner. Row projects is on the left side of the table ("Projecten"). And this most also be filtered with the status off the project. The status are the three checkbox's that you will find on the top. Checkbox name's are CkxKlaar,CkxLopende and CkxNietIngeplant.

The buttons at the top right are the sites and these are two separate tables.

enter image description here

I tried several codes but even filtering on projects did not work.

Private Sub TbxZoekFunctie_Change()

    Dim i As Long
    Dim arrList As Variant

    Me.LsbProjecten.Clear
    If Blad2.Range("A" & ProjectenITM.Rows.Count).End(xlUp).Row > 1 And Trim(Me.TbxZoekFunctie.Value) <> vbNullString Then
        arrList = ProjectenITM.Range("A1:G" & ProjectenITM.Range("A" & ProjectenITM.Rows.Count).End(xlUp).Row).Value2
        For i = LBound(arrList) To UBound(arrList)
            If InStr(1, arrList(i, 7), Trim(Me.TbxZoekFunctie.Value), vbTextCompare) Then
                Me.LsbProjecten.AddItem arrList(i, 7)
            End If
        Next i
    End If
    If Me.LsbProjecten.ListCount = 1 Then Me.LsbProjecten.Selected(0) = True

End Sub




Aucun commentaire:

Enregistrer un commentaire