list box question

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

Can I populate my list box with the files named in a sub-directory on my
hard drive, and if yes, when I click on one of these file names can I
open the file programmatically directly from the on click event of the
listbox?

Thanks for your help
Joanne
 
Joanne,

Populate the listbox and show the userform:

Sub GetFiles()
Dim myArray() As String
With Application.FileSearch
.NewSearch
.LookIn = "C:\Excel"
.FileName = "*.xls"
If .Execute() > 0 Then
ReDim myArray(0 to .FoundFiles.Count - 1)
For i = 0 To .FoundFiles.Count - 1
myArray(i) = .FoundFiles(i + 1)
Next i
Load Userform1

Userform1.ListBox1.List = myArray
Userform1.ListBox1.Selected(0) = False
Userform1.Show
End If
End With
End Sub

Then, to open the file when you click it:

Private Sub ListBox1_Change()
Userform1.Hide
Application.Workbooks.Open Userform1.ListBox1.List(Userform1.ListBox1.ListIndex)
Unload Userform1
End Sub


HTH,
Bernie
MS Excel MVP
 
Back
Top