VBA: cancel file selection

[kweaver]

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.

 

[Fluff]

Can you post the code you're using?

 

[kweaver]

Part of the 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).

 

[Fluff]

What have you declared Fname as?

 

[kweaver]

Fname As String

 

[Fluff]

In that case your code works for me. In what way doesn't it work for you?

 

[kweaver]

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!

 

[kweaver]

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.

 

[Fluff]

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.

 

[kweaver]

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.