VBA二次运行Sub后ListBox内容未更新问题排查与解决

我有一个Excel工作簿，包含多张工作表，所有工作表的首列存储姓名信息。在名为Input的工作表上有一个命令按钮，点击后触发searchRostSheets过程，流程如下：

• 通过InputBox提示用户输入搜索字符串searchStr（该变量在Module 1中声明为Public searchStr As String）
• 遍历所有名称包含Roster的工作表，匹配符合条件的姓名
• 将匹配结果展示在UserForm9的selectionListBox中
• 用户选择ListBox中的条目后，跳转至对应条目所在的工作表

问题现象：首次运行过程一切正常，但第二次输入新的搜索内容后，ListBox仍显示第一次的结果；点击Cancel按钮后第三次运行，ListBox才会正确展示新的搜索结果。尝试在卸载UserForm的函数中添加selectionListBox.Clear，但未解决问题。

传递搜索字符串的代码（Module 1） #

Sub searchRostSheets()
    
    searchStr = InputBox("Please enter the text you wish to search for:", "Search")
    
'   Check for existence of roster data

    Dim rosterSh As Worksheet
    Dim numStudents As Integer, totalStudents As Integer
    totalStudents = 0
    
    For Each rosterSh In ThisWorkbook.Worksheets
        Set rosterSh = ThisWorkbook.Sheets(rosterSh.Name)
        
        If rosterSh.Name Like "*Roster*" Then
            numStudents = rosterSh.Range("A" & rosterSh.Range("A:A").Rows.Count).End(xlUp).row
        
            If numStudents = 1 Then
                ' MsgBox "You have not entered any roster data for " & rosterSh.Name & ".", vbExclamation, "Alert"
                GoTo nextSheet
            Else
                ' Proceed
            End If
            
            numStudents = numStudents - 1
        End If
        
        totalStudents = totalStudents + numStudents
        
nextSheet:
    Next rosterSh
    
    If totalStudents = 0 Then
        MsgBox "You have not entered any roster data." & vbNewLine & vbNewLine & _
        "You must enter roster data to perform this task.", vbExclamation, "Alert"
        Exit Sub
    Else
        ' Proceed
    End If

    UserForm9.Show
    
Exit Sub

End Sub

UserForm9及ListBox相关代码 #

Private Sub UserForm_Initialize()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Dim sheet As Worksheet, rosterSh As Worksheet
    Dim numStudents As Integer, searchCell As Range, i As Integer, listboxValCheck As Boolean
    Dim firstSearchAddr As String, lastSearchAddr As String
    Dim searchDict As Object
    Set searchDict = CreateObject("Scripting.Dictionary")
        
    For Each sheet In ThisWorkbook.Sheets
    
        If sheet.Name Like "*Roster*" Then
            Set rosterSh = ThisWorkbook.Sheets(sheet.Name)
             
            firstSearchAddr = "A2" 'in cell A2
            lastSearchAddr = "C" & rosterSh.Columns("C").Find("*", , xlValues, , xlByRows, xlPrevious).row
            
            
                
                For Each searchCell In rosterSh.Range(firstSearchAddr & ":" & lastSearchAddr)
                    Dim listboxStr As String
                    If InStr(searchCell.Value, searchStr) > 0 Or InStr(UCase(searchCell.Value), UCase(searchStr)) > 0 Or InStr(LCase(searchCell.Value), LCase(searchStr)) > 0 Then
                        Select Case searchCell.Column
                            Case 1
                                listboxStr = searchCell.Value & "   [" & Replace(rosterSh.Name, " Roster", "") & "]" & "                                   " & searchCell.Address
                            Case 2
                                listboxStr = searchCell.Offset(0, -1).Value & "   [" & Replace(rosterSh.Name, " Roster", "") & "]" & "                                   " & searchCell.Offset(0,-1).Address
                            Case 3
                                listboxStr = searchCell.Offset(0, -2).Value & "   [" & Replace(rosterSh.Name, " Roster", "") & "]" & "                                   " & searchCell.Offset(0,-2).Address
                        End Select
                        
                        If searchDict.Exists(listboxStr) Then
                            ' listboxStr is already in dictionary
                        Else
                            searchDict.Add listboxStr, searchDict.Count
                            selectionListBox.AddItem listboxStr
                        End If
EndfOfLoop:
                    End If
                    
                Next searchCell
                       
        End If
        
    Next sheet
        
    searchDict.RemoveAll
        
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub

Private Sub OK_Click()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

'   Set sheet to current sheet
    Dim rosterSh As Worksheet, rosterShName As String
    Dim selectedStu As String, stuAddr As String
    
    If selectionListBox.ListIndex < 0 Then
        MsgBox "You did not make a selection. Please make a selection or press " & Chr(34) & "Cancel" & Chr(34) & " to continue.", vbExclamation, "Alert"
        Exit Sub
    Else
        selectedStu = selectionListBox.List(selectionListBox.ListIndex)
        rosterShName = Trim(Split(Replace(Split(selectedStu, "[")(1), "]", ""), "$")(0)) & " Roster"
        stuAddr = Split(Trim(Split(selectedStu, "]")(1)), "$")(2)
    End If
    
    Set rosterSh = ThisWorkbook.Sheets(rosterShName)
    rosterSh.Activate
    rosterSh.Rows(stuAddr & ":" & stuAddr).Select

    Call unloadUserForm9
    UserForm9.Hide
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
Exit Sub

End Sub

Private Sub Cancel_Click()
    If Not UserForm9 Is Nothing Then
        Call unloadUserForm9
        UserForm9.Hide
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    UserForm9.Hide
    Call unloadUserForm9
    If CloseMode = 0 Then
        ' DO NOTHING
    End If
End Sub

根本原因 #

UserForm的Initialize事件仅在窗体第一次加载到内存时触发。当前代码中，点击OK或Cancel后只是调用Hide隐藏窗体，并未彻底卸载（Unload）。第二次调用UserForm9.Show时，窗体已经在内存中，不会重新触发Initialize事件，因此仍使用第一次的searchStr生成旧结果；直到点击Cancel调用unloadUserForm9卸载窗体后，第三次运行才会重新加载窗体并触发Initialize，使用新的搜索字符串。

具体修复步骤 #

1. 确保每次显示窗体前先卸载旧实例 #

修改searchRostSheets过程中UserForm9.Show的代码，先卸载已存在的窗体实例，再重新加载：

' 替换原有的UserForm9.Show代码
On Error Resume Next
Unload UserForm9
On Error GoTo 0
UserForm9.Show

2. 完善unloadUserForm9过程（如果未定义则新增） #

确保彻底清理窗体资源，包括清空ListBox并释放对象：

Sub unloadUserForm9()
    If Not UserForm9 Is Nothing Then
        UserForm9.selectionListBox.Clear
        Unload UserForm9
        Set UserForm9 = Nothing ' 彻底释放对象
    End If
End Sub

3. 优化搜索逻辑（可选） #

原代码中大小写判断可以简化为vbTextCompare参数，减少冗余代码：

' 替换原有的If判断
If InStr(1, searchCell.Value, searchStr, vbTextCompare) > 0 Then

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