ListBox列表在第二次运行具有不同InputBox变量值的Sub之后相同

Question

所以我有一个电子表格，里面有一堆在第一列列出名字的表格。在一张工作表Input工作表上，我有一个打开Sub的命令按钮，该按钮提示用户通过searchStr = InputBox(“Please enter text to search for:”,”Search for name”)请求字符串来搜索每张工作表上的名称列，其中searchStr被声明为具有Public searchStr As String的全局变量。

提交此输入后，将筛选所有表单中的所有名称以进行字符串匹配，并将其返回到UserForm中的ListBox。然后，用户从填充ListBox的名称中进行选择，并重定向到包含其选择的图纸。

这一切都很好——直到用户第二次运行子。InuptBox再次提示用户输入，用户有义务并提交，但只会遇到完全相同的ListBox名称列表，尽管他们的输入大不相同，潜在的匹配项也大不相同……因此用户点击Cancel按钮。

然后在第三次运行时，它会自我更正，ListBox会按照它应该的方式填充，也就是说是正确的。

发生了什么事，我该如何解决？

以下是将搜索字符串传递给UserForm:的代码

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

这是与UserForm和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

我在Module 1中还有一个Public searchStr As String声明。

编辑2：我尝试在卸载功能中添加selectionListBox.Clear，但没有成功。

Answer 1

使用

Unload UserForm9

以便在单击事件中正确销毁用户窗体对象的实例。只有当您使用Unload(非.Hide(关闭用户表单时，当再次显示用户表单时才会触发initialize事件，这将正确填充列表框。

使用卸载(无论是否在宏中(后，必须删除宏中所有与UserForm相关的代码，如UserForm9.卸载调用后隐藏。否则，这将始终使用全局变量的旧值再次触发initialize事件。如果卸载用户表单，请确保在用户表单中的所有操作结束时卸载，除非您想再次初始化它。

请不要在代码中使用跳转点。尤其是你的吊环设计得很糟糕。