CODEWITHSUNDEEP
vbaexcellistboxuserform
aL a
aL a

Reputation: 21

UserForm populate listbox with criteria

I have a database with five columns in a different workbook called db.xls : userid - date - name - subject - comments

I fill this with a data entry form (works fine). The problem is in a UserForm, I need to retrieve specific entries on a specific criteria in a listbox. I need to put the name AND subject in two textboxes or dropdown lists and with these two criteria populate a listbox, in a ascending order by date + the subject and when I click on any listbox entries, it lookup and give me the comment that goes with that line in textbox.

CODE : 

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

Upvotes: 0

Views: 1539

Answers (1)

aL a
aL a

Reputation: 21

Adding this code after ListBox1.AddItem solved the problem :

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

Upvotes: 1

Related Questions