Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA: cancel file selection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: cancel file selection

    I have a macro that prompts a user to select a file that is subsequently saved as a sheet and the data used in calculations.
    If the user cancels at the prompt to select a file, how do I avoid an error and just exit the macro?

    I suspect it's something to do with OnError, but don't know the placement or syntax to use.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: cancel file selection

    Can you post the code you're using?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: cancel file selection

    Part of the code:

    Code:
    Dim statements
    Application.ScreenUpdating = False
       Set DestWbk = ThisWorkbook
       Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
       If Fname = "False" Then Exit Sub
    etc
    etc
    When this prompt appears, a user might decide to skip selecting a file, so they click "Cancel" (maybe they forgot which one to use or something).
    Last edited by kweaver; Sep 22nd, 2019 at 03:53 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: cancel file selection

    What have you declared Fname as?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: cancel file selection

    Fname As String

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: cancel file selection

    In that case your code works for me. In what way doesn't it work for you?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: cancel file selection

    That code (which is a macro called "GetFile") where the user is given the prompt is called from another macro called "RunMacro".

    If the user selects a file, the next macro that's run in "RunMacro" is one that does a variety of calculations on the input file and creates another sheet in the workbook.
    That macro is called "FixData"...and it errors if the user clicked "CANCEL" on the file selection prompt.

    So, I'm pretty sure I WHERE the problem is but not how to fix it.

    The FixData macro is still called even when the user clicks CANCEL on the file selection prompt. I don't want that call to be made but want a complete exit from both the FixData macro AND the macro calling it ("RunMacro").
    I guess my overall structure isn't ideal!

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: cancel file selection

    I've seen a similar question asked elsewhere but there are never any applicable solutions. It seems (at least to me) that there should be a "simple" grand exit upon a condition.
    If MACRO_A calls 4 other macros, MAC1, MAC2, MAC3, then MAC4 -- in sequence and, along the way, the user does something (in my case clicks "cancel" on a "select a file" prompt) that I thought there must be a way to totally bail out of not only the currently running macro (e.g., suppose that's MAC3 in the sequence), but out of the calling master macro as well so as NOT to proceed to MAC4, etc. Maybe not.
    Last edited by kweaver; Sep 22nd, 2019 at 07:04 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA: cancel file selection

    Two options that I can think of.
    1) Use End rather than Exit Sub, but that will also destroy any global/public variables. (Not recommended)
    2) Use a global variable & if the user hits Cancel set the variable to TRUE, then check that variable in the master macro after each sub event has been called.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: cancel file selection

    Where do I declare a global variable? Is it before the initial macro? Then, what do I use to detect the CANCEL key has been clicked? Thanks for your helpful suggest. If I can learn how to do this, I suspect that's the key to the issue!!

    I have a button on the master sheet that calls the RunMacros routine...then, that routine has the other 3 or 4 sequential macro calls.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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