userforms, specify multiple files locations

[kanton]

OK, need some coding help please!!

I have 3 userforms. First one has three OptionButtons for the user to select their location. They then click an OK button. Second one has four CommandButtons that ask the user which data to import. The third userform consists of a listbox that gets filled with file names per the user's input in the first two userforms. I need help modifying the below codes to make sure the files being loaded into the listbox come from the correct server location based upon the answer in the first userform.

So, if user selects location A in the first userform (options A, B or C), then selects data X (options X, Y, Z or cancel) to be pulled in, the third userform needs to be filled with files from the location that corresponds with A and data files for X.
Right now I have the below code which fills the listbox, but it is only set for location A ("C:\Documents and Settings\username\My Documents\NA_Inventory"). How can I set it to pick up what the user inputs in userform 1?

Private Sub SacImport_Click()
    Unload Me
    ' Declare filesearch object.
    Set fs = Application.FileSearch
    ' Set folder to search. *****THIS IS WHERE I NEED HELP****
    fs.LookIn = "C:\Documents and Settings\username\My Documents\NA_Inventory"
    ' Set file name to search for.
    fs.Filename = "*US.xls"
    ' Execute the file search, and check to see if the file(s) are present.
    If fs.Execute > 0 Then
       ' Redimension the array to the number of files found.
       ReDim myarray(fs.FoundFiles.Count)
       ' Loop through all found file names and fill the array.
       For i = 1 To fs.FoundFiles.Count
          myarray(i) = fs.FoundFiles(i)
       Next i
    Else
       ' Display message if no files were found.
       MsgBox "No files were found"
    End If
    ' Loop through the array and fill the list box on the UserForm.
    For i = 1 To fs.FoundFiles.Count
       UserForm3.ListBoxFILES.AddItem myarray(i)
    Next i
    ' Display the UserForm.
    UserForm3.Show
End Sub

I have one other question, but I may be able to figure it out myself after someone helps with this.

Thanks in advance.

 

[pbornemeier]

First, note that Application.Filesearch has been deprecated in Office 2007, so you code will not work with Excel 2007 (and presumeably later versions). Check out the FileScriptingObject for an alternate method of collecting file info.

For your current code, the following, replicated and modified for the additional option buttons should work:

If UserForm1.OptionButton1 Then
    fs.LookIn = Environ("userprofile") & "\My Documents\NA_Inventory\"
    fs.Filename = "*US.xls"
End If

 

[kanton]

Thanks for the tip.

I must not be doing something right, because I changed my code to the following and it shows all files, no matter which option buttons on Form1 are clicked. Also, since I now know this is possible, I change it to the following: UserForm1 has 6 optionsButtons, 3 in one frame and 3 in another.
I want the code to do the following, but it's not working properly. Any help would be great!!

This code is for the 'OK' button on the first form. Is this the correct place to put it?

Private Sub CommandButton2_Click()
     Unload Me
    ' Declare filesearch object.
    Set fs = Application.FileSearch
    ' Set folder to search.
    fs.LookIn = "C:\Documents and Settings\username\My Documents\NA_Inventory"
'*****THIS IS WHERE I NEED HELP!!*****
If UserForm1.OptionButton1 And UserForm1.OptionButton4 Then
    ' Set file name to search for.
    fs.Filename = "*US.xls"
    
    ElseIf UserForm1.OptionButton2 And UserForm1.OptionButton5 Then
        fs.Filename = "*UK.xls"
    
     ElseIf UserForm1.OptionButton3 And UserForm1.OptionButton6 Then
        fs.Filename = "*MA.xls"
End If
    
    ' Execute the file search, and check to see if the file(s) are present.
    If fs.Execute > 0 Then
       ' Redimension the array to the number of files found.
       ReDim myarray(fs.FoundFiles.Count)
       ' Loop through all found file names and fill the array.
       For i = 1 To fs.FoundFiles.Count
          myarray(i) = fs.FoundFiles(i)
       Next i
    Else
       ' Display message if no files were found.
       MsgBox "No files were found"
    End If
    ' Loop through the array and fill the list box on the UserForm.
    For i = 1 To fs.FoundFiles.Count
       UserForm2.ListBox1.AddItem myarray(i)
    Next i
    ' Display the UserForm.
    UserForm2.Show
End Sub

UserForm2 has the listbox of files.

 

[pbornemeier]

The 'Unload Me' at the start of the code closes userform 1 so the values of its option buttons are lost and default to false. You probably want to have 'Unload Me' just before the last line which displays the new userform.

The current logic sets fs.Filename to a particular value only if both pairs of buttons are the same. What is supposed to happen if they are different? Why do you want button pairs?

Unless your username is 'username' then the fs.LookIn line will not work - you may want to use the ENVIRON option I mentioned in post #2.

Code for a form is usually best located on that form's codepage.

If you change the (Name) of the OK button to "cmdOK" (by turning on properties with the F4 key) then this line:

Private Sub CommandButton2_Click()

would read like this

Private Sub cmdOK_Click()

it makes code a lot easier to follow

 

[kanton]

Thank you so much!

The files will actually stored on a network so I won't need to use ENVIRON, but that is great to know for any future use. Right now I just had it coded for local files and took my username out.

I will have more options (optionbutton 1 and 3, 1 and 4, 1 and 5, 2 and 4, 2 and 5, 2 and 6 etc), but was just trying to get the first ones working properly before moving on.

Also, I agree with the CmdOK. Only reason I didn't have it on this was because I had just added the cmd button and was too lazy to change the name until it was right.

Again, thank you very much for your help. I will test this in a bit.