List Box in Userforms

[Waterpolo1]

Hi Team,

Please could someone help with the following issue.

Context

- I have a useform which requires the user to upload documents to the userform. I have it at the moment so the filepath is shown.

Question

What is the best way to allow for multiple documents to be selected and thus multiple file paths to populate an excel cell in a workbook?

Current method

To find the file:

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)

If IsArray(FilePathArray) Then
Dim ArraySize As Long
ArraySize = UBound(FilePathArray, 1) - LBound(FilePathArray, 1) + 1
Dim ArrayPosition As Long
For ArrayPosition = 1 To ArraySize
If Not FilePathArray(ArrayPosition) = Empty Then

UserForm.ComboBoxEvidence.AddItem (FilePathArray(ArrayPosition))
End If
Next ArrayPosition
ElseIf FilePathArray <> False Then
'userform name is "userform"
UserForm.ComboBoxEvidence.AddItem (FilePathArray)
End If



This populates files selected into a list but has the following issues:

1.) Doesn't allow the user to delete a file if they make a mistake
2.) You can only submit one excel file to a spreadsheet

Many thanks for your support.

Joseph

 

[Jaafar Tribak]

Hi Team,

Please could someone help with the following issue.

Context

- I have a useform which requires the user to upload documents to the userform. I have it at the moment so the filepath is shown.

Question

What is the best way to allow for multiple documents to be selected and thus multiple file paths to populate an excel cell in a workbook?

Current method

To find the file:

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)

If IsArray(FilePathArray) Then
Dim ArraySize As Long
ArraySize = UBound(FilePathArray, 1) - LBound(FilePathArray, 1) + 1
Dim ArrayPosition As Long
For ArrayPosition = 1 To ArraySize
If Not FilePathArray(ArrayPosition) = Empty Then

UserForm.ComboBoxEvidence.AddItem (FilePathArray(ArrayPosition))
End If
Next ArrayPosition
ElseIf FilePathArray <> False Then
'userform name is "userform"
UserForm.ComboBoxEvidence.AddItem (FilePathArray)
End If

This populates files selected into a list but has the following issues:

1.) Doesn't allow the user to delete a file if they make a mistake
2.) You can only submit one excel file to a spreadsheet

Many thanks for your support.

Joseph

Something like this maybe :

Dim FilePathArray As Variant

Private Sub CommandButton2_Click()

'GetOpenFile MultiSelect will return an Array if more than one is selected
FilePathArray = Application.GetOpenFilename(, , , , MultiSelect:=True)
If IsArray(FilePathArray) Then
Dim ArraySize As Long
ArraySize = UBound(FilePathArray, 1) - LBound(FilePathArray, 1) + 1
Dim ArrayPosition As Long
For ArrayPosition = 1 To ArraySize
If Not FilePathArray(ArrayPosition) = Empty Then
UserForm.ComboBoxEvidence.AddItem (FilePathArray(ArrayPosition))
End If
Next ArrayPosition
ElseIf FilePathArray <> False Then
'userform name is "userform"
UserForm.ComboBoxEvidence.AddItem (FilePathArray)
End If

[COLOR=#0000ff]If IsArray(FilePathArray) Then
    Range("a1") = "Selected Files"
    Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(FilePathArray, 1)) = Application.Transpose(FilePathArray)
End If[/COLOR]
End Sub

[COLOR=#0000cd]Private Sub CommandButton3_Click() [B]'<== Undo Button[/B]
    If IsArray(FilePathArray) Then
        If Range("a" & Rows.Count).End(xlUp).Address <> "$A$1" Then
            Range("a" & Rows.Count).End(xlUp).Offset(-UBound(FilePathArray, 1) + 1).Resize(UBound(FilePathArray, 1)).ClearContents
        End If
    End If
End Sub[/COLOR]

The code assumes that the cells that are populated with the file names are in Column A of the activesheet.