I have a button on a sheet that calls a sub, and that sub calls other subs,
I might even have a third-level sub in there somewhere, and each of those
subs have plenty of code.

I just found out that there is a potential problem with files that are
auto-loaded behind the scenes; now I need to pop a messagebox to the user
with a few key pieces of info to confirm whether the source files are valid.
Unfortunately, the best place to do that is within an embedded sub.

So while I've used exit for and exit sub before, my understanding is that
they each only exit the current "level". If I exit this embedded sub, then
I'll still have the main calling sub run, which I don't want (because it
will try to run the bad data).

What is the appropriate way to stop or reset the processing of any/all VBA
code from within an embedded sub? it could be a serious re-write to try to
set a variable in multiple places to skip each chunk of code, so I'd prefer
to just stop the code altogether and let the user upload an appropriate
file, then restart.

Thanks for any help,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi,

Try End (instead of Exit Sub).

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"KR" wrote:

I have a button on a sheet that calls a sub, and that sub calls other subs,
I might even have a third-level sub in there somewhere, and each of those
subs have plenty of code.

I just found out that there is a potential problem with files that are
auto-loaded behind the scenes; now I need to pop a messagebox to the user
with a few key pieces of info to confirm whether the source files are valid.
Unfortunately, the best place to do that is within an embedded sub.

So while I've used exit for and exit sub before, my understanding is that
they each only exit the current "level". If I exit this embedded sub, then
I'll still have the main calling sub run, which I don't want (because it
will try to run the bad data).

What is the appropriate way to stop or reset the processing of any/all VBA
code from within an embedded sub? it could be a serious re-write to try to
set a variable in multiple places to skip each chunk of code, so I'd prefer
to just stop the code altogether and let the user upload an appropriate
file, then restart.

Thanks for any help,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Pass back a return value to the caller, and if a certain value exit that
also

Call Sub2(rtn)
If rtn = -1 Then
Exit Sub
...

Sub Sub2(ByRef nReturn as Long)
... do some stuff

Call Sub3(nReturn)
If nReturn = -1 Then
Exit Sub
...

End Sub

Sub Sub3(ByRef nReturn as Long)
... do some stuff
If some error Then
nReturn = -1
Exit Sub
...

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"KR" wrote in message
...
I have a button on a sheet that calls a sub, and that sub calls other
subs,
I might even have a third-level sub in there somewhere, and each of those
subs have plenty of code.

I just found out that there is a potential problem with files that are
auto-loaded behind the scenes; now I need to pop a messagebox to the user
with a few key pieces of info to confirm whether the source files are
valid.
Unfortunately, the best place to do that is within an embedded sub.

So while I've used exit for and exit sub before, my understanding is that
they each only exit the current "level". If I exit this embedded sub, then
I'll still have the main calling sub run, which I don't want (because it
will try to run the bad data).

What is the appropriate way to stop or reset the processing of any/all VBA
code from within an embedded sub? it could be a serious re-write to try to
set a variable in multiple places to skip each chunk of code, so I'd
prefer
to just stop the code altogether and let the user upload an appropriate
file, then restart.

Thanks for any help,
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

If you have static or public variables, be aware that "End" will reinitialize
them.

"Chris Marlow" wrote:

Hi,

Try End (instead of Exit Sub).

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"KR" wrote:

I have a button on a sheet that calls a sub, and that sub calls other subs,
I might even have a third-level sub in there somewhere, and each of those
subs have plenty of code.

I just found out that there is a potential problem with files that are
auto-loaded behind the scenes; now I need to pop a messagebox to the user
with a few key pieces of info to confirm whether the source files are valid.
Unfortunately, the best place to do that is within an embedded sub.

So while I've used exit for and exit sub before, my understanding is that
they each only exit the current "level". If I exit this embedded sub, then
I'll still have the main calling sub run, which I don't want (because it
will try to run the bad data).

What is the appropriate way to stop or reset the processing of any/all VBA
code from within an embedded sub? it could be a serious re-write to try to
set a variable in multiple places to skip each chunk of code, so I'd prefer
to just stop the code altogether and let the user upload an appropriate
file, then restart.

Thanks for any help,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

You might like to consider raising an error using eg err.raise 513 (513
is the start of the userdefined errors) and then you can trap this
however far up the call-stack you want to go.

It may give you a more controlled process

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=530511