Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Chooser (Excel 2000)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: File Chooser (Excel 2000)

    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

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Chooser (Excel 2000)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: File Chooser (Excel 2000)

    Check out this MS Tutorial on creating a custom form.

    Post back with any specific questions you might have

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: File Chooser (Excel 2000)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Chooser (Excel 2000)

    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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Chooser (Excel 2000)

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

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

    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: File Chooser (Excel 2000)

    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:<path>"
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •