Userform listbox not populating when userform is first opened

[mpettine]

Hello,
I have a userform that contains a listbox (AccomList) that displays data from a named range (AccomResults). When I first open the userform, the listbox is most times completely blank, including the headers. Sometimes it's populated, and sometimes not. When I click on another listbox in the userform (EmpList), and then close the userform and re-open it, the listbox (AccomResults) will show populated. I can see that the data in the named range is populated correctly, so the listbox in the userform is not populating for some reason (including the headers). I noticed when I click around the worksheet, and then I go to open the userform, that's when the listbox is not populated. I can't understand why clicking around the worksheet and then opening the userform would cause the listbox to not populate until i click the other listbox and close it and then re-open it. The other listbox (EmpList) has no problems at all, only the AccomList listbox. Also, sometimes the AccomList listbox is not completely populated (some rows of data are populated and some aren't), so it can also be populated incompletely even though the name range where the data is coming from is complete.

Here is the code where list box AccomList is loaded. Thank you for any insight you can provide!


Sub OpenEmpForm()
EEListLoad
Sheet3.Range("AE3:AY999").ClearContents 'clear previous accommodation results
EmpFrm.Show
End Sub

Sub EEListLoad()
With Sheet2
lastRow = .Range("A999").End(xlUp).Row
If lastRow < 4 Then Exit Sub
If EmpFrm.OpenEmp.Value = True Then .Range("I3").Value = True Else .Range("I3").Value = "<>" 'Set Active
.Range("J3").Value = "*" & EmpFrm.EmpSearch.Value & "*" 'Employee Search
.Range("A3:G" & lastRow).AdvancedFilter xlFilterCopy, CriteriaRange:=.Range("I2:J3"), CopyToRange:=.Range("N2:O2"), Unique:=True
lastResultRow = .Range("N999").End(xlUp).Row
If lastResultRow < 3 Then Exit Sub
If lastResultRow < 4 Then GoTo NoSort
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Sheet2.Range("N3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Sort
.SetRange Sheet2.Range("N3:O" & lastResultRow) 'Set Range
.Apply 'Apply Sort
End With
.Calculate 'Calculate to Refresh Employee Results
NoSort:
End With
AccomList_Load
End Sub

Sub Employee_Load()
With EmpFrm
SelRow = .EmpList.ListIndex + 3
EmpRow = Sheet2.Range("O" & SelRow).Value
If EmpRow = 0 Then Exit Sub
For EmpCol = 1 To 6
Set EmpField = .Controls("Field" & EmpCol)
EmpField.Value = Sheet2.Cells(EmpRow, EmpCol).Value
Next EmpCol
End With
AccomList_Load 'Load accommodations
End Sub

Sub AccomList_Load()
If EmpFrm.Field1.Value = "" Then Exit Sub 'Exit on No Emp ID
With Sheet3
lastRow = .Range("A99999").End(xlUp).Row
If lastRow < 4 Then Exit Sub
.Range("AA3").Value = EmpFrm.Field1 'Set Emp ID Criteria
On Error Resume Next
.Range("A3:W" & lastRow).AdvancedFilter xlFilterCopy, CriteriaRange:=.Range("AA2:AA3"), CopyToRange:=.Range("AE2:AY2"), Unique:=True
lastResultRow = .Range("AE99999").End(xlUp).Row
If lastResultRow < 3 Then Exit Sub
If lastResultRow < 4 Then GoTo NoSort
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Sheet3.Range("AE3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 'Sort
.SetRange Sheet3.Range("AE3:AY" & lastResultRow) 'Set Range
.Apply 'Apply sort
End With
NoSort:
.Calculate
On Error GoTo 0
End With
End Sub

And here are the properties for the AccomList listbox

 

[Trevor G]

Welcome to the forum how have you got on?

When the user form is shown/activated is where you add the code to fill the list box.

When you add the code into your thread please use the code tags as it makes it easier for us to read and then point to any issues which makes suggesting a solutions easier to recommend.