File Chooser (Excel 2000)

Topic

New Reply

Hi All,

Is there a way to automatically prompt the user for a choice selection from a drop-down and then process that file AND … have this happen when they open the file?

Right now, the user opens the file, clicks on a particular cell which has the choices (via data validation). After making the selection, the user clicks on a macro button that then opens the selected Study Data file and processes it.

I need to impress my users and, unfortunately, something like this would!

Any help would be appreciated…
–cat

Reply | Quote

Replies

You could create userform with the combobox range linked to the same list that is currently in datavalidation
On workbook_open, run the userform,
based on selection,
open the file
process any macros.

If you need more help, you will have to provide some more details

Steve

Reply | Quote

Hi Steve, thanks.
I have never used a UserForm (or the Combo Box within it) … and I’m a self-taught VB user (which could explain a lot of things!). If you could help with some basic code for a UserForm that uses a combo box … I could probably take it from there. (Hopefully!)
Thanks,
–cat

Reply | Quote

Check out this MS Tutorial on creating a custom form.

Post back with any specific questions you might have

Steve

Reply | Quote

Check out this MS Tutorial on creating a custom form.

Post back with any specific questions you might have

Steve

Reply | Quote

Cindy are you looking to provide the user with a choice of only a certain set of files, or are you looking for all files in a certain directory, or what? Depending on what you are doing, there might be some cookie cutter alternatives to a userform.

Reply | Quote

John, I’m looking for all files in a directory, but it could be a named range that I maintain within the excel file.
Cindy

Reply | Quote

The code below will display a standard File Open dialog box and then opens the file selected by the user:

Dim vFileToOpen As Variant
    vFileToOpen = Application.GetOpenFilename("xls,*.xls")
    If vFileToOpen  False Then
        Workbooks.Open Filename:=vFileToOpen
    End If

Reply | Quote

The code below will display a standard File Open dialog box and then opens the file selected by the user:

Dim vFileToOpen As Variant
    vFileToOpen = Application.GetOpenFilename("xls,*.xls")
    If vFileToOpen  False Then
        Workbooks.Open Filename:=vFileToOpen
    End If

Reply | Quote

Since Legare has explained what I was thinking that might fit your requirements, just two more notes:

GetOpenFilename has a Multiselect Option which allows the user to select multiple files which are returned as an array and which you can then process.

You can guide the user to a specific directory by preceding the GetopenFilename statement with

ChDir “C:”

Reply | Quote

Since Legare has explained what I was thinking that might fit your requirements, just two more notes:

GetOpenFilename has a Multiselect Option which allows the user to select multiple files which are returned as an array and which you can then process.

You can guide the user to a specific directory by preceding the GetopenFilename statement with

ChDir “C:”

Reply | Quote

John, I’m looking for all files in a directory, but it could be a named range that I maintain within the excel file.
Cindy

Reply | Quote

Cindy are you looking to provide the user with a choice of only a certain set of files, or are you looking for all files in a certain directory, or what? Depending on what you are doing, there might be some cookie cutter alternatives to a userform.

Reply | Quote

Hi Steve, thanks.
I have never used a UserForm (or the Combo Box within it) … and I’m a self-taught VB user (which could explain a lot of things!). If you could help with some basic code for a UserForm that uses a combo box … I could probably take it from there. (Hopefully!)
Thanks,
–cat

Reply | Quote

You could create userform with the combobox range linked to the same list that is currently in datavalidation
On workbook_open, run the userform,
based on selection,
open the file
process any macros.

If you need more help, you will have to provide some more details

Steve

Reply | Quote