Returning to Main Macro from a Called Procedure after a MsgBox/Exit Sub

[liquidmettle]

Hi Everyone,

I have a question on moving from Sub-procedures back to the main macro.

I have a database/dashboard with a Userform to manage data entry. Many of the userform fields have exclusion criteria. I.e. A message box would appear if you entered an insurance company but also checked "no insurance" to tell the user they have to choose one or the other and then exiting the sub (but not the userform) so they can make the correction.

Because this userform has 7 periods of followup after a clients intake, I had to breakdown parts of the code into sub-procedures because I received the "Procedure Too Large" error.

The problem I have is that message box exclusion criteria within the sub-procedures exits the entire userform (not allowing the user to make the correction). The message box will show correctly, but upon exiting that message box, the whole userform closes too.

Is there a way to use the "Exit Sub" after a message box in a sub-procedure and return to the larger macro without the userform closing?

Please Login or Register  to view this content.

Thanks in advance!

[Logit]

.
Try calling the UserForm in question from the other sub ...

[liquidmettle]

Hi Logit,

The genius simplicity of your solution made me kick myself (one of those "of course!!" moments) but it appears to have had the following effects:

If the call is after the "Exit Sub" following the message box, the userform closes as it did before (with no discernible difference).

If I put the call before "Exit Sub" following the message box, I get an error that the userform cannot be called/duplicated because its already open.

Thoughts?

[Logit]

.
Are you "HIDE" the form or are you "Unload Me" the form ?

Unload Me should close it and remove it from memory.

[liquidmettle]

Hi Logit,

If there are no validation issues, the form is unloaded. With any of the validation prompts, I always have it "Exit Sub" so the user can fix the issue in the userform.

If a validation issue occurs within one of the called procedures, this appears to simply end everything instead of allowing the user to correct the issue in the userform and hit the "Save" button again.

[liquidmettle]

Hi Everyone,

I have found a work-around that suits me for now. I simply moved all validation from the sub-procedures into the main Sub, so that no validation occurs in a called procedure. This appears to have worked. I'm marking this complete for moment, though I wouldn't mind knowing if there was a way to achieve what I had originally asked.

[rorya]

None of the code you posted would close the userform so it's hard to say. Did you use End on its own somewhere?

[liquidmettle]

Hi Rorya,

End was only used at the end of the main sub.

What I imagine was happening is that using "Exit Sub" in the called procedure only exited that single called procedure. I think the main sub simply continued chugging along once that called procedure was stopped. Since the validation prompts in question were contained within each sub procedure, as long as there weren't other validation issues in the main sub, it simply completed the operation. But calling the main sub from one of the called procedures didn't work because it was still in the process of running.

[rorya]

In that case I would have recommended changing the subs into functions that return True if all OK and False otherwise. You could then test each result before proceeding with the rest of the code.