Create User Form

L

LEXCERM

Hello All,

I want to be able to create a user form with the following:-

1) Drop down box to show all listed directories i.e. c:\, a:\ etc
2) List box to show all files listed in the selected directory (above

3) A command button to open the file selected


I am familiar with userforms but not sure what the coding is to achiev
this.

Any advice will be greatfully received.

Regards,
PAUL
 
D

Dave Peterson

Why not just show a file|open dialog box and let the user traverse the folders:

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim SourceWkbk As Workbook

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set SourceWkbk = Workbooks.Open(Filename:=myFileName)
''' rest of your code

End Sub
 
L

LEXCERM

Thanks Dave for your prompt reply.

I use the method that you suggested all the time, but I have com
across a situation whereby I have coded the Open dialog box to pop-u
midway through a macro. If a user hit's ESC whilst the dialog box i
showing, the macro bombs-out and leads to complications. I have se
"enablecancel" to disabled so that a user cannot interrupt the macr
whilst it's running, but it seems to ignore the ESC key when the dialo
box is showing, and stops the macro.

The other reason that I would like to create a user form is to expan
my knowledge in this area. Knowledge is power!!! lol

Regards,
PAUL
 
D

Dave Peterson

I've never seen hitting escape kill the rest of the macro (with a simple
validation later--it should work like hitting cancel).

To get the folder,

If you're using xl2002+, you can read about:
Application.FileDialog
in VBA's help.

If before, then Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

Or John Walkenbach's:
http://j-walk.com/ss/excel/tips/tip29.htm

And maybe read about application.filesearch in VBA's help to get a list of
files.
 
Top