How to populate a userform listbox from excel data

Waterpolo1

New Member
Joined
Sep 8, 2015
Messages
21
Process so far, the problem one is step 3 only:
1. Get file path of document and populate into a listbox (Code below)

Private Sub CommandButton2_Click()
'GetOpenFile MultiSelect will return an Array if more than one is selected
On Error Resume Next

Dim FilePathArray As Variant
FilePathArray = Application.GetOpenFilename(, , , , MultiSelect:=True)
UserForm.EvidenceListBox.AddItem (FilePathArray(1))

' If IsArray(FilePathArray) Then

Dim ArraySize As Long
ArraySize = UBound(FilePathArray, 1) - LBound(FilePathArray, 1)
Dim ArrayPosition As Long
For ArrayPosition = 1 To ArraySize
If Not FilePathArray(ArrayPosition) = Empty Then
UserForm.EvidenceListBox.AddItem (FilePathArray(ArrayPosition))
End If

Next ArrayPosition

2. Submit data to text box in sheet (Code below)
For i = 0 To EvidenceListBox.ListCount - 1
ws.Range("P" & LastRow).Value = ws.Range("P" & LastRow).Value & " | " & EvidenceListBox.List(i)
Next I

3. PROBLEM ONE - I have a button which when I click, retrieve data from the excel sheet into a lisbox. It finds the right list by finding the right ID. current code which doen't work is below:

EvidenceListBox1.Text = ws.Cells(i, 16)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
You can also remove those loops like
Code:
Private Sub CommandButton2_Click()
'GetOpenFile MultiSelect will return an Array if more than one is selected

Dim FilePathArray As Variant
FilePathArray = Application.GetOpenFilename(, , , , MultiSelect:=True)
Me.EvidenceListBox.List = FilePathArray
Ws.Range("P" & lastRow).Value = Join(FilePathArray, "|")
End Sub
 

Waterpolo1

New Member
Joined
Sep 8, 2015
Messages
21
Thanks Roderick - That only adds whats in the text box ie one big list. In the loop (Step 2), if there were multiple attachments I separated them out with "|". I need to basically, take this "|" out and just have the file paths in a list again, rather than one big list - any ideas?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
How about
Code:
Me.EvidenceListBox.List = Split(Ws.Cells(i, 16), "|")
 

Waterpolo1

New Member
Joined
Sep 8, 2015
Messages
21
Thanks Fluff, doesn't seem to work - It pops up with the browsing for documents again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,261
Office Version
365
Platform
Windows
The code from post#3 is designed to replace the code you supplied in the OP
 

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top