如何在VBA中实现ListBox的当日/当月维度日期筛选

嗨，我来帮你搞定这个日期筛选的问题！针对ListBox1展示当日数据、ListBox2展示当月数据的需求，咱们可以通过VBA编写针对性的筛选逻辑，既把符合条件的数据填充到对应的ListBox里，还能同步更新那6个统计标签的数值。

先理清楚核心的日期判断逻辑：

• 当日筛选：判断数据的Date列是否等于今天的日期（注意要统一日期格式，避免单元格格式差异导致判断出错）
• 当月筛选：判断数据的Date列是否在当前月份的第一天到最后一天之间

下面是具体的实现代码，你可以把这段代码放到用户窗体的初始化事件里，或者绑定到一个刷新按钮的点击事件中：

Private Sub RefreshListBoxes()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim todayDate As Date, firstDayOfMonth As Date, lastDayOfMonth As Date
    
    ' 指定数据所在工作表
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 初始化日期变量
    todayDate = Date
    firstDayOfMonth = DateSerial(Year(todayDate), Month(todayDate), 1)
    lastDayOfMonth = DateSerial(Year(todayDate), Month(todayDate) + 1, 0)
    
    ' 清空ListBox和统计计数
    ListBox1.Clear
    ListBox2.Clear
    Dim danDaily As Integer, danMonthly As Integer
    Dim lisaDaily As Integer, lisaMonthly As Integer
    Dim totalDaily As Integer, totalMonthly As Integer
    danDaily = 0: danMonthly = 0
    lisaDaily = 0: lisaMonthly = 0
    totalDaily = 0: totalMonthly = 0
    
    ' 遍历数据行（从第2行开始，跳过表头）
    For i = 2 To lastRow
        Dim rowDate As Date
        ' 确保单元格内容是日期格式
        If IsDate(ws.Cells(i, "D").Value) Then
            rowDate = ws.Cells(i, "D").Value
            
            ' 处理当月数据：填充ListBox2并统计
            If rowDate >= firstDayOfMonth And rowDate <= lastDayOfMonth Then
                ListBox2.AddItem ws.Cells(i, "A").Value ' 添加ID
                ListBox2.List(ListBox2.ListCount - 1, 1) = ws.Cells(i, "B").Value ' 添加Name
                ListBox2.List(ListBox2.ListCount - 1, 2) = ws.Cells(i, "C").Value ' 添加Status
                ListBox2.List(ListBox2.ListCount - 1, 3) = ws.Cells(i, "D").Value ' 添加Date
                
                totalMonthly = totalMonthly + 1
                Select Case ws.Cells(i, "B").Value
                    Case "Dan"
                        danMonthly = danMonthly + 1
                    Case "Lisa"
                        lisaMonthly = lisaMonthly + 1
                End Select
                
                ' 同时判断是否为当日数据：填充ListBox1并统计
                If rowDate = todayDate Then
                    ListBox1.AddItem ws.Cells(i, "A").Value
                    ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Cells(i, "B").Value
                    ListBox1.List(ListBox1.ListCount - 1, 2) = ws.Cells(i, "C").Value
                    ListBox1.List(ListBox1.ListCount - 1, 3) = ws.Cells(i, "D").Value
                    
                    totalDaily = totalDaily + 1
                    Select Case ws.Cells(i, "B").Value
                        Case "Dan"
                            danDaily = danDaily + 1
                        Case "Lisa"
                            lisaDaily = lisaDaily + 1
                    End Select
                End If
            End If
        End If
    Next i
    
    ' 更新统计标签内容（记得改成你实际的控件名称）
    LabelDanDaily.Caption = "Dan当日计数: " & danDaily
    LabelDanMonthly.Caption = "Dan当月计数: " & danMonthly
    LabelLisaDaily.Caption = "Lisa当日计数: " & lisaDaily
    LabelLisaMonthly.Caption = "Lisa当月计数: " & lisaMonthly
    LabelTotalDaily.Caption = "总当日计数: " & totalDaily
    LabelTotalMonthly.Caption = "总当月计数: " & totalMonthly
End Sub

最后给你几个小提示：

• 提前给ListBox设置好列数（比如4列对应ID、Name、Status、Date），可以在属性窗口里修改ColumnCount为4，也可以在代码开头添加ListBox1.ColumnCount = 4和ListBox2.ColumnCount = 4
• 把代码里的标签名称替换成你实际使用的控件名称，比如你的Dan当日计数标签叫Label1，就改成对应名称
• 如果需要打开窗体就自动加载数据，把Call RefreshListBoxes放到UserForm_Initialize事件里即可

备注：内容来源于stack exchange，提问作者Shiela