Folder Contents listed in Combo box

[leerem]

Hi all,
As mentioned in the Subject line, How would I list the contents of
a Folder eg. D:\Any\Contacts\?????. and have this info displayed in a Combo
Box within a worksheet, wherby the User is then able to pick a file to be
loaded in Excel, ensuring of course that only .xl folders are displayed. If
the folder has no content or does not exist, then to load a file by the name
of Contacts1.xls

Regards

Lee

 

[leerem]

Sorry make that a list box not a combo box

 

[Mike H]

Hi,

You didn't say where you list box was. This assumes iot's on a worksheet and
the code will execute when the workshet is activated. Change the directory to
suit

Private Sub Worksheet_Activate()
ListBox1.Clear
MyPath = "C:\"
MyName = Dir$(MyPath & "*.xls") 'only looks for xl files
With ListBox1
Do While MyName <> ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .XLS from end of name
.AddItem MyName
MyName = Dir
Loop
End With
End Sub

Mike

 

[leerem]

Hi Mike,

So assuming the list box was to be placed in Cell D12, Could I place this
code in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

as I already have code in this procedure could I add it at the base so the
list box would only appear it the user selected it

 

[Mike H]

Hi,

You can choose any event you want to call the code. If you used
selection_change you could limit it to when the actual selection was d12

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> ("$D$12") Then Exit Sub
ListBox1.Clear
MyPath = "C:\"
MyName = Dir$(MyPath & "*.xls") 'only looks for xl files
With ListBox1
Do While MyName <> ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .XLS from end of name
.AddItem MyName
MyName = Dir
Loop
End With

End Sub

Mike