Solved: Code to stop all subsequent code and return to userform

[Shortz]

Hi guys,

This maybe a very simple question but i currently have a form which when the "process" button is clicked it triggers this code:

[vba]Private Sub ProcessBut_Click()

Application.ScreenUpdating = False

Select Case SQForm.WG.Value

Case ""
Exit Sub

Case Else
Process.PPLevelCheck
Process.PPCheck
Process.OutSheetName
Process.OutSheetCopy
Process.Checker
Process.Queryruns

End Select

Application.ScreenUpdating = True

End Sub[/vba]
most of these macros are error checking such as OutSheetName which checks if the inputted name for a new worksheet already exists.

Currently these nested macros are coded to 'Exit Sub' when certain conditions are met eg. worksheet already exists. When it does this it merely exits the particular nested macro and proceeds to the next one on the list, rather than taking me back to the form for the user to amend the error.

What is the best way to deal with this? should i just use the 'End' command?

IDEALLY what i want is that when one of the nested macros exit then it will display the message i've written in them and go back to the userform.

thanks in advance!

[mdmackillop]

Rather than a Sub, you could make the test code a Function whick will return True/False,
Then something like this for each test
[VBA]
If Not Process.PPLevelCheck Then Exit Sub
[/VBA]

[SamT]

You could Dim a Public Flag Variable As Boolean, then set it in each sub before exiting. Be sure to reset it after reading it.

[Shortz]

So you mean that at the end of each nested macro have

[VBA]Flag = true[/VBA]

And in the larger macro you would have an If flag = true then proceed statement? Would you make that into a function then?

eg.

[VBA]function Flagger()
dim flag as boolean
If flag = false then
exit sub
end if[/VBA]

but would this then just recreate the problem? or should i not make it a function and hardcode it in?

eg. Flag statement
Nested macro1
flag statement
nested macro2
etc

[mdmackillop]

Here's a Function example
[VBA]
Sub test()
If Not ShExist("Test1") Then
MsgBox "does not exist"
Exit Sub
Else
MsgBox "Exists"
End If
End Sub

Function ShExist(sh) As Boolean
On Error Resume Next
ShExist = True
If Sheets(sh) Is Nothing Then ShExist = False
End Function
[/VBA]

[Shortz]

I have this code already embedded into one of the nested macros
eg. PPlevelcheck
and it exits that macro fine, but the rest still run.

I think i'll make a global flag variable that gets checked after each macros run. (true/false). If i went with this idea, could i make it into a function or does it need to be hard coded in this macro? (will the function just make the same problem as before??)

Could i also ask about some optimisations?
If i place the applicationscreenupdate code at the beginning of this macro, will it apply to all the nested ones?

[SamT]

Don't need a function. The code to use with a public Boolean Variable is;

In Called Procedure:

'yada yada
 
'Something bad here
Flag = False
Exit Sub
 
'All was good, continue
'yada yada
 
Flag = True
End Sub

In your Master Procedure

'Yada Yada
 
Flag = False
Call SumSub()
 
Do Events
   If Not Flag Then 
     Call Sub OOPS()
     Stop Procedure.Me
   End If
 
'yada yada

And

Sub OOPS(Parameters)
Messages = "Warning! Warning! Danger Will Robinson!"
'Yada Yada

In my opinion:
Boolean Flags are about the only variable Types that should be used As Public. If you need Public Properties, you should use Property Procedures.

Flags themselves don't have to be just Booleans.

Public Success As String

The Setting Procedure

Success = Me.Name & "True" 'Since the name is expected to be known
'OR
Success =  Me.Name  & ErrorCode

You can create a User Defined Type (UDT)

Type tySuccess
suBool As Boolean
suCaller As String(32)
suError_Code As Long 
suMsg As String(64)
End Type
 
Public Success As tySuccess

To set it

With Success
.suBool = True
.suCaller = Me.Name
.suMsg = "Yay!"
End With

Read it again, I did say "About." Those more compex structure should only be used when it is critical that more than a boolean value be used. It's just a "Bad Thing."

A Public StringVar can be real handy because it will carry boolean values, and once in a while you may want it to carry some string value. Until that happens you can just

StringVar = True
'OR 
StringVar = BooleanVar
'OR
If StringVar Then