VBA如何避免修改绑定数据源时ListBox的ListIndex选中焦点自动跳转

该ListBox为多列控件，绑定「Data_Output」工作表的数据，示意图如下：

问题背景 #

需求是点击按钮时，将选中ListIndex对应条目的状态设置为「Closed」、「完成日期」设置为当前日期，最初编写代码如下：

Private Sub FinishButton_Click()

If Sheets("Data_Output").Cells(Me.ListBox1.ListIndex + 2, 1).Value = "Open" Then
    Sheets("Data_Output").Cells(Me.ListBox1.ListIndex + 2, 4).Value = Date
    Sheets("Data_Output").Cells(Me.ListBox1.ListIndex + 2, 1).Value = "Closed"
Else
    MsgBox "Runsheet is not open. Runsheet should have open status to be closed."
End If

End Sub

运行时出现异常：执行修改日期的代码后，ListIndex选中项会自动跳转到列表首行，导致后续修改状态的代码使用了错误的索引值。

问题原因 #

因为ListBox直接绑定了工作表作为数据源，修改工作表单元格内容后，ListBox会自动刷新绑定的数据源，刷新过程中原选中状态会被重置，ListIndex默认回到首行对应的0值，后续实时读取该属性就会拿到错误值。

正确实现方案 #

操作前先将选中的索引缓存到变量中，所有单元格操作都使用缓存的索引值，修改完成后如果需要保留原选中状态，再手动将ListIndex赋值回缓存值即可，可正常运行的代码如下：

Private Sub FinishButton_Click()

Dim selected_row As Integer
selected_row = Me.ListBox1.ListIndex

If Sheets("Data_Output").Cells(selected_row + 2, 1).Value = "Open" Then
    Sheets("Data_Output").Cells(selected_row + 2, 4).Value = Date
    Sheets("Data_Output").Cells(selected_row + 2, 1).Value = "Closed"
    Me.ListBox1.SetFocus
    Me.ListBox1.ListIndex = selected_row
Else
    MsgBox "Runsheet is not open. Runsheet should be open in order to be closed."
End If

End Sub

优化建议 #

可以对代码做小幅调整，提升健壮性：

1. 开头先判断ListIndex是否为-1（无选中条目），避免无选中时操作报错
2. 将索引变量类型改为Long，避免列表行数超过Integer上限（32767）时溢出
3. 缓存工作表对象，减少重复读取工作表的开销
   优化后代码：

Private Sub FinishButton_Click()
    ' 校验是否有选中条目
    If Me.ListBox1.ListIndex = -1 Then
        MsgBox "请先选中要操作的条目"
        Exit Sub
    End If
    
    Dim selected_row As Long
    Dim dataSht As Worksheet
    Set dataSht = ThisWorkbook.Sheets("Data_Output")
    selected_row = Me.ListBox1.ListIndex

    If dataSht.Cells(selected_row + 2, 1).Value = "Open" Then
        dataSht.Cells(selected_row + 2, 4).Value = Date
        dataSht.Cells(selected_row + 2, 1).Value = "Closed"
        ' 恢复原选中状态
        Me.ListBox1.SetFocus
        Me.ListBox1.ListIndex = selected_row
    Else
        MsgBox "当前条目状态不是Open，仅Open状态的条目可标记为完成。"
    End If
End Sub

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