Alexia, this is the code for a listbox with foldernames

Hi Alexia,

This was my situation : the name of the file to open was always the same, only the folder was different. So I have made a listbox with the available folders in a certain "root".

What you need is :

- a worksheet with a cell named as "FOLDERS"
- an userform called "Userform1" with :
- a listbox called "listbox1"
- a OK-button called "btnOK"
- a Cancel-button called "btnCancel"
- code for the buttons
- code for the userform

There is a lot of comment between the code to get you started.

I Hope this will help you to find your solution...
Bruno
--------------------------
1) code for the buttons :

Private Sub btnCancel_Click()
End
End Sub

Private Sub btnOK_Click()
Me.Hide
End Sub
--------------------------
2) code for the userform :

Sub LocateFolders()

' Display the names in the path that represent directories.
' Set the path.
MyPath = "C:\"
' Set the filename
MyFile = "My_file_to_open.xls"

' Be sure that MyPath end with "\"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
' Be sure that MyFile begins with "\"
If Left(MyFile, 1) <> "\" Then MyFile = "\" & MyFile
' Clear the previous list of folders in the worksheet
' 1000 folders would be enough, I think ...
Range(Range("Folders").Offset(1, 0), Range("Folders").Offset(1000, 0)).Clear

' Retrieve the first entry.
Myname = Dir(MyPath, vbDirectory)

Counter = 1
' Start the loop.
Do While Myname <> ""
' Ignore the current directory and the encompassing directory.
If Myname <> "." And Myname <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then
' If it is a directory write the foldername to the worksheet
Range("Folders").Offset(Counter, 0) = Myname
Counter = Counter + 1
End If
End If
' Get next entry.
Myname = Dir
Loop

' Sort the folders
Range(Range("Folders").Offset(1, 0), Range("Folders").Offset(1000, 0)).Select
Selection.Sort Key1:=Range("Folders"), Order1:=xlAscending

' Clear the previous list of folders
UserForm1.listbox1.Clear

' Fill the listbox
For Counter = 1 To Application.CountA(Range(Range("Folders").Offset(1, 0), Range("Folders").Offset(1000, 0)))
UserForm1.listbox1.AddItem Range("Folders").Offset(Counter, 0)
Next

' Let see the result in the userform
UserForm1.Show

' After closing the userform
File_to_open = MyPath
File_to_open = File_to_open & UserForm1.listbox1.Value
File_to_open = File_to_open & MyFile

' File_to_open contains the FullName of the file to open...
MsgBox File_to_open

' Open the file

Workbooks.Open FileName:=File_to_open

End Sub
--------------------------