excelvbacomboboxlistboxspreadsheet

How to sort data of Sheet1 using a Combobox with values from Sheet2?


I have here 2 sheets. 1 sheet is for the main data. The other sheet is for the Teams of the Name entries.

Sheet1 (Blanks are on purpose - to see outcome as well if there are blanks)

A B C
Name Date Added Date Modified
Anna 3/11/2025 3/18/2025
Mav 3/11/2025 3/12/2025
Lisa 3/14/2025 3/13/2025
Ron 3/11/2025 3/14/2025
Mary 3/12/2025 3/15/2025
Kurt 3/13/2025 3/17/2025
3/15/2025
Kevin 3/16/2025

Sheet2

A B
Team Name
Lucy Anna
Lucy Mav
Peter Lisa
Peter Ron
Nory Mary
Nory Kurt
Carl Mona
Carl Kevin

ListBox:

wrongTeam

I would like to choose teams coming from Sheet2. I have a code here below but will give me error of "Type mismatch".

showList is called during ComboBox Change:

Sub showList()
    Dim ws As Worksheet, colList As Collection
    Dim arrData, arrList, i As Long, j As Long
    Dim targetTeam As Variant
    ' *** 
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    Dim arr: arr = ws2.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = Empty
    Next
   ' ***

    Set colList = New Collection
    Set ws = Worksheets("Sheet1")
    arrData = ws.Range("A1:E" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
    For i = 2 To UBound(arrData)
            targetTeam = Application.VLookup((arrData(i, 2)), ws2.Range("B1").CurrentRegion.Value, -1, False)
                If dict.exists(arrData(i, 1)) And cmbTeam = targetTeam Then
                    colList.Add i, CStr(i)
                End If

    Next
    ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
    For j = 1 To 5
        arrList(1, j) = arrData(1, j) ' header
        arrList(1, 4) = "Date Added Duration"
        arrList(1, 5) = "Date Modified Duration"
        For i = 1 To colList.count
                arrList(i + 1, j) = arrData(colList(i), j)
                    Dim dateA As Variant
                    Dim dateB As Variant
                    Dim dateC As Variant
                    Dim difference1 As Long
                    Dim difference2 As Long
                
                ' Assign values to the dates
                dateA = arrList(i + 1, 2)
                dateB = arrList(i + 1, 3)
                dateC = Format(Now, "m/d/yyyy")
                
                ' Calculate the difference in days
                difference1 = DateDiff("d", dateA, dateC) 'date today minus date added
                
                If Not dateA = "" Then
                    If difference1 > 1 Then
                    arrList(i + 1, 4) = difference1 & " days"
                    Else
                    arrList(i + 1, 4) = difference1 & " day"
                    End If
                Else
                    arrList(i + 1, 4) = "Missing"
                End If
                
                difference2 = DateDiff("d", dateB, dateC) 'date today minus date modified
                
                If Not dateB = "" Then
                    If difference2 > 1 Then
                    arrList(i + 1, 5) = difference2 & " days"
                    Else
                    arrList(i + 1, 5) = difference2 & " day"
                    End If
                Else
                    arrList(i + 1, 5) = "Missing"
                End If
        Next
    Next
    With Me.ListBox1
        .Clear
        .ColumnCount = UBound(arrData, 2)
        .list = arrList
    End With
End Sub

Type Mismatch Error

errorCode

Desired:

desiredOutcome


Solution

  • Filter the dictionary entries

    Sub showList()
    
        Dim ws As Worksheet, ws2 As Worksheet, colList As Collection
        Dim arr, arrData, arrList, i As Long, j As Long, n As Long
        Dim dt As Date, dif As Long, targetTeam As Variant
    
        targetTeam = "Nory" ' replace "Nory" with cmbTeam
        Set ws2 = Sheets("Sheet2")
        arr = ws2.Range("B1").CurrentRegion.Value
        
        ' names for team
        Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(arr)
            If arr(i, 1) = targetTeam Then
                dict(arr(i, 2)) = i
            End If
        Next
       
        ' data for team
        Set ws = Sheets("Sheet1")
        arrData = ws.Range("A1:E" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        Set colList = New Collection
        For i = 2 To UBound(arrData)
            If dict.exists(arrData(i, 1)) Then
                colList.Add i, CStr(i)
            End If
        Next
        
        ReDim arrList(1 To colList.Count + 1, 1 To 5)
        arrList(1, 4) = "Date Added Duration"
        arrList(1, 5) = "Date Modified Duration"
        
        For j = 1 To 3
            arrList(1, j) = arrData(1, j) ' header
           
            For n = 1 To colList.Count
                i = n + 1
                arrList(i, j) = arrData(colList(n), j)
                
                ' calc add and mod diffs
                If j > 1 Then
                    If arrList(i, j) = "" Then
                        arrList(i, j + 2) = "Missing"
                    Else
                        dt = arrList(i, j)
                        dif = DateDiff("d", dt, Now) 'date today minus date add/mod
                        arrList(i, j + 2) = dif & IIf(dif > 1, " days", " day")
                    End If
                End If
            Next
        Next
        
        With Me.ListBox1
            .Clear
            .ColumnCount = UBound(arrList, 2)
            .List = arrList
        End With
    End Sub