Getting Creative

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

Hi

I need some help.

Is it possible to have a validation drop down list that is populated by the
file names of a pre-set folder location/name?

Is it then possible to select a file from the validation list that will then
open the file for inputting information?

Thanks

Gordon
 
set we have a set of files in a folder C:\test:

alpha.xls
beta.xls
gamma.xls
temp.xls
tempx.xls

Pick a column and enter:

alpha
beta
gamma
temp
tempx

Then in another cell, say A1, enter a data validation pull-down that will
select from the list. Finally, in another cell, enter:

=HYPERLINK("file:///c:\test\" & A1 & ".xls",A1)

So the user picks the file in A1 and this produces a nice clickable link in
the other cell.
 
Hi.

The solution needs to be more intuitive. The folder contents might change
without notice. Is there a way that a column can automatically update to a
folders contents?

G
 
Hi G

I think I would use a userform with a listbox populated with the files to
select form, and call the userform from a button on the worksheet.

Setup a userform with a userform with a ListBox named ListBox1 and a
CommandButton named CommandButton1 and inset the code below in the codesheet
for the userform:

Const MyPath As String = "C:\Temp\"
Private Sub CommandButton1_Click()
'If Me.ListBox1.Value <> "" Then
MyFile = MyPath & ListBox1.Value
' Exit Sub
'End If
Workbooks.Open Filename:=MyFile
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim MyName As String

MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first entry.
Me.ListBox1.AddItem MyName

Do While MyName <> "" ' Start the loop.
Me.ListBox1.AddItem MyName
MyName = Dir ' Get next entry.
Loop
End Sub

Hopes this helps
 
Back
Top