So I have a spreadsheet with a bunch of sheets that list names in the first column. On one sheet, the Input sheet, I have a command button that opens a Sub that prompts the user to perform a search of the names columns on each sheet by requesting a string via searchStr = InputBox(“Please enter text to search for:”,”Search for name”), where searchStr is declared as a global variable with Public searchStr As String.
Upon submission of this input, all names in all sheets are sifted through for string matches and returned to a ListBox in a UserForm. The user then makes a selection from the names that populate the ListBox and is redirected to the sheet which contains their selection.
This all works great – until the user runs the sub the second time. The InuptBox, once again, prompts the user for input and the user obliges and submits only to be met with the exact same ListBox list of names even though their input was vastly different and the potential matches also wildly dissimilar… and so the user hits the Cancel button.
And then on the third run, it corrects itself and the ListBox populates as it should, which is to say correctly.
What’s going on, and how can I fix it?
Here's the code that passes the search string to the 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
And here is the code that pertains to the UserForm with the 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
I also have a Public searchStr As String declaration in Module 1.
EDIT 2: I’ve tried adding a selectionListBox.Clear in the unloading function, but that didn’t work.
Use
Unload UserForm9
to correctly destroy the instance of the user form object on your click events. Only if you use Unload (not .Hide) to close the userform the initialize event will trigger when showing the userform again which will fill the listbox correctly.
After you used Unload (in a macro or not) you have to remove all UserForm related code in your macros such as UserForm9.Hide after that unloading call. This would otherwise always trigger the initialize event again with the old value of your global variable. If you unload your userform, make sure, you do it at the end of all doings in the userform unless you want to initialize it again.
And please do not make use of jump points in your code. Especially your loops are terribly designed with this jump marks.