Excel表单Listbox VBA：实现最新日期时间条目置顶显示

问题背景 #

点击CommandButton1可将格式化后的日期（写入ExcelEntryDB工作表C列）、时间（写入D列）及TextBox3内容（写入E列）录入，同时在ListBox1展示条目。当前代码运行正常，但最新条目出现在列表底部，需滚动查看。尝试插入排序语句时触发Sort method Range class failed错误，需求是让最新条目置顶，实现按日期时间倒序展示。

原有代码 #

Private Sub CommandButton1_Click()
 Dim Row As Long
 Row = ThisWorkbook.Sheets("ExcelEntryDB").Cells(Rows.Count, "C").End(xlUp).Row
 Me.ListBox1.ColumnCount = 3
 Me.ListBox1.ColumnHeads = True
 Me.ListBox1.ColumnWidths = "75;75;75"

 If Row > 1 Then
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E" & Row
 Else
  Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E2" & Row
 End If

 Dim sh As Worksheet
 Set sh = ThisWorkbook.Sheets("ExcelEntryDB")
 Dim n As Long

 n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
     sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")
   sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss AM/PM")
   sh.Range("E" & n + 1).Value = Me.TextBox3.Value

 Me.TextBox3.Value = ""

End Sub

报错信息 #

Sort method Range class failed

最终解决代码 #

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("ExcelEntryDB")
     Dim n As Long

 n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
     sh.Range("C" & n + 1).Value = Format(Date, "mm/dd/yyyy")
     sh.Range("D" & n + 1).Value = Format(Time, "hh:nn:ss AM/PM")
     sh.Range("E" & n + 1).Value = Me.TextBox3.Value
 Me.TextBox3.Value = ""

 Me.ListBox1.ColumnCount = 3
 Me.ListBox1.ColumnHeads = True
 Me.ListBox1.ColumnWidths = "75;75;75"

 Me.ListBox1.Rowsource = "ExcelEntryDB!C2:E" & n + 1 '修正范围，包含新录入行
 sortByTimeInTwoColumns()

End Sub
    
Sub sortByTimeInTwoColumns()
  Dim ws As Worksheet, lastR As Long, rngFlt As Range, arrT, filtCol As Long
  
  Set ws = ThisWorkbook.Sheets("ExcelEntryDB") '明确指定工作表，避免ActiveSheet风险
  lastR = ws.Range("C" & ws.Rows.Count).End(xlUp).Row '获取C列最后一行

  Set rngFlt = ws.Range("C2:E" & lastR) '待排序的数据范围
  filtCol = ws.Cells(1, Split(rngFlt.Address, "$")(3)).Column + 1 '获取右侧空白辅助列

  '将日期+时间合并为数值数组（Excel中日期存为整数，时间存为小数，相加后完整代表时间戳）
  arrT = Evaluate(rngFlt.Columns(1).Address & "+" & rngFlt.Columns(2).Address)
  
  ws.Cells(2, filtCol).Resize(UBound(arrT, 1)).Value2 = arrT '写入辅助列
  ws.Range("C2:F" & lastR).Sort Key1:=ws.Cells(2, filtCol), Order1:=xlDescending, Header:=xlNo '按辅助列倒序排序
  ws.Cells(2, filtCol).Resize(UBound(arrT, 1)).ClearContents '清除辅助列内容

End Sub

解决思路说明 #

1. 修复排序报错：原代码未指定工作表导致Range引用混乱，且单独按日期排序会忽略时间维度；解决代码明确指定操作工作表，同时用日期+时间的合并值作为排序依据，保证时间顺序准确。
2. 实现条目置顶：通过辅助列生成完整时间戳，按该值倒序排序，让最新录入的日期时间条目排在最前。
3. 修正数据范围：调整ListBox的RowSource，确保加载排序后的最新数据范围。

内容的提问来源于stack exchange，提问作者Shiela