How to use multiple selections from VBA Userform Multiselect Listbox in path

Question

I have created a VBA userform, by manipulating code i found from a website, for coworkers to transfer files from selected folder from one listbox to another folder in a second listbox. The folders that populate in the listboxes change daily. It works fine for both listboxes with fmSingleSelect but i cannot figure out how to run it properly with a fmMultiSelect property on the second listbox (Yes, i changed the property to fmMultiSelect on the second listbox).

It would save time to be able to multiselect the projects folder and run the transfers simultaneously.

Below is the code for single select and commented out some code i was working with for multiselect

Also an image is below code

Thanks

Private Sub CmdBtn_transfer_Click()

    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim Value As String
    Dim i As Integer

    FromPath = "C:\Users\us-lcn-dataprep03\Desktop\Production files\" & (Me.ListBox1) '<< Change
    ToPath = "\\bego.hb\MED_PRODUCTION\USA_Datapreparation\" & (Me.ListBox2)   '<< Change

' For i = 0 To ListBox2.Items.Count - 1
   ' If ListBox2.Items(i).Selected = True Then
       ' Val = ListBox2.Items(i).Value
   ' End If
'Next i

    FileExt = "*.sli*"  '<< Change

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    If FSO.FolderExists(ToPath) = False Then
        MsgBox ToPath & " doesn't exist"
        Exit Sub
    End If

    FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
    MsgBox "You can find the files from " & FromPath & " in " & ToPath
End Sub

Userform list boxes

Are you trying to copy all the files from one directory to multiple destination directories? — YowE3K
– YowE3K, Commented Sep 21, 2016 at 1:01
It currently copies all the .sli file from the source folder to the destination folder. i want to copy the files from the source folder to multiple selected destination folders — A Rossi
– A Rossi, Commented Sep 21, 2016 at 1:12

YowE3K · Accepted Answer · 2016-09-21 01:29:22Z

1

The following code is a "minimal change" alteration to your code so that it should handle copying the files from one directory to multiple directories:

Private Sub CmdBtn_transfer_Click()

    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim Value As String
    Dim i As Integer

    FromPath = "C:\Users\us-lcn-dataprep03\Desktop\Production files\" & (Me.ListBox1) '<< Change

    FileExt = "*.sli*"  '<< Change

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    For i = 0 To ListBox2.ListCount - 1
        If ListBox2.Selected(i) Then
            ToPath = "\\bego.hb\MED_PRODUCTION\USA_Datapreparation\" & (Me.ListBox2.List(i))    '<< Change

            If Right(ToPath, 1) <> "\" Then
                ToPath = ToPath & "\"
            End If

            If FSO.FolderExists(ToPath) = False Then
                MsgBox ToPath & " doesn't exist"
                Exit Sub
            End If

            FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
            MsgBox "You can find the files from " & FromPath & " in " & ToPath
        End If
    Next i

End Sub

All I did was move your commented out code re looping through the selected items in ListBox2 so that it was wrapped around the parts of the code which are affected by the ToPath. (Note: The MsgBox is within the loop - you may wish to move it outside the loop but, if you do, you may want to make the message more generic - e.g. "Your files have been moved as requested".)

I also corrected some mistakes in your commented code:

ListBox2.Items.Count should be ListBox2.ListCount
ListBox2.Items(i).Selected should be ListBox2.Selected(i)
ListBox2.Items(i).Value should be ListBox2.List(i)

edited Sep 21, 2016 at 1:29

answered Sep 21, 2016 at 1:14

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Comintern Over a year ago

ListBox2.Items(i).Selected should be ListBox2.Selected(i).

YowE3K Over a year ago

@Comintern - Thanks - you can tell I don't use ListBoxes very often - I just assumed the OP's code was correct, other than not knowing how to do the looping.

Comintern Over a year ago

That's probably why it was commented out. ;-)

A Rossi Over a year ago

I got a "Compile error: Method or data member not found"

YowE3K Over a year ago

@Comintern - there are a few other errors in the commented out code too

|

Collectives™ on Stack Overflow

How to use multiple selections from VBA Userform Multiselect Listbox in path

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related