UserForm用标准填充列表框

Question

在一个名为db.xls：userid - date - name -的不同工作簿中，有一个包含五列的数据库。

我用一份数据输入表填写这个表格(很好)。问题是在UserForm中，我需要在列表框中检索特定条件上的特定条目。我需要将name和subject放在两个文本框或下拉列表中，使用这两个条件填充一个列表框，按date + subject的升序排列，当我单击任何列表框条目时，它会查找并给出与textbox中该行对应的注释。

代码:

Private Sub searchbutton_Click()

Dim nwb As Workbook

Application.ScreenUpdating = False

Set nwb = Workbooks.Open("C:\db.xls", _
False, True)

txtsubject.Text = ""

Set xSht = nwb.Sheets("notes")
    Lastrow = xSht.Range("C" & Rows.Count).End(xlUp).Row
    strSearch = txtname.Text
                Set aCell = xSht.Range("C1:C" & Lastrow).Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

  If Not aCell Is Nothing And txtsubject.Value = "" Then
     GoTo refvalid
 Else
MsgBox "no entries for " & txtname.Value & ". ", Title:="result of search"
    End If
Exit Sub
refvalid:

row_number = 0
ListBox1.Clear
Do
DoEvents
row_number = row_number + 1
item_in_review = nwb.Sheets("notes").Range("C" & row_number)
If item_in_review = txtname.Text Then
txtsubject.Text = nwb.Sheets("notes").Range("A" & row_number)
'concatenated date + subject in column F
ListBox1.AddItem nwb.Sheets("notes").Range("F" & row_number) 

End If
Loop Until item_in_review = ""
'in module, sortlistbox to order then ascending
Run "SortListBox", ListBox1, 0, 1, 1

nwb.Close False ' close the source workbook without saving changes
Set nwb = Nothing
Application.ScreenUpdating = True
With ListBox1

.ColumnCount = 5
.MultiSelect = fmMultiSelectSingle
.TextColumn = 1
.BoundColumn = 1
If ListBox1.Value <> "" Then
TextBox35.Value = " [" & ListBox1.Text & "] : " & ListBox1.Value
End If

End With
End Sub

'====================================================

Private Sub ListBox1_Click()
If ListBox1.Value <> "" Then
TextBox5.Value = " [" & ListBox1.Text & "] : " & ListBox1.Value
End If

End Sub

Answer 1

在ListBox1.AddItem解决了问题后添加此代码：

ListBox1.List(ListBox1.ListCount - 1, 1) = nwb.Sheets("notes").Range("F" & row_number)