Exit Sub Macro Help

[EricL89]

Kind of new to macros and VBA, but I'm starting to get the hang of it. I'm currently using Excel 2007.

My document has one module, and the main sub runs three other subs. i.e.:

Sub Master()

MiniSub1
MiniSub2
MiniSub3

End Sub


In the sub I'm calling MiniSub1, I have a MsgBox which I'm using vbOKCancel. With that, I have:

Dim ErrorMessage As String

ErrorMessage = MsgBox("You messed up",vbOKCancel)

If ErrorMessage = vbOK Then

***(some code here)***

Else
Exit Sub
End If
***(followed by a lot more code)***

The Exit Sub and the code as a whole work great, but I'd like it to exit the entire sub (Master); just stop everything there and end the macro. Instead, after clicking "cancel" the macro proceeds to MiniSub2.

This is intended to be similar to an error message, where clicking "Ok" lets you continue, while clicking "Cancel" lets you stop everything to fix the error before running it again.

I'd expect it'd be some code inserted in Sub Master(), but I have no idea what to do.

Is this possible? And if so, thanks for your help!

Eric

 

[JLGWhiz]

Have you tried using 'End' instead of 'Exit Sub'? That should stop the whole lot.

 

[Rick Rothstein]

One possibility is to turn your mini-subs into Boolean functions and have the function return True if you want the main sub to end. Here is the idea for MiniSub1 which I'll rename to MiniFunction1 (not necessary to do, but I like my procedure names to mean something, so calling a function a "sub" bothers me).

Function MiniFunction1() As Boolean
  Dim ErrorMessage As String

   ErrorMessage = MsgBox("You messed up",vbOKCancel)

   If ErrorMessage = vbOK Then
    
    ***(some code here)***

  Else
    [COLOR=#FF0000][B]MiniFunction1[/B][/COLOR]
    Exit Sub
  End If
  ***(followed by a lot more code)***
Exit Function

You would do a similar change with all the Subs that you wanted to signal the main Sub it should end. Then in your main Sub would change only those mini-subs you converted to functions (those mini-subs that do not have to signal the main sub to end can remain exactly as you have them now)... I'll assume MiniSub1 and MiniSub3 were converted to functions and MiniSub2, because it does not have to signal the main sub to end, remains as is)...

Sub Master()

[B]   If MiniFunction1 Then Exit Sub[/B]
   MiniSub2
[B]   If MiniFunction3 Then Exit Sub[/B]

 End Sub

 

[Osvaldo Palmeiro]

Hi, Eric.
Another way,

Sub Master()
  Dim ErrorMessage As String
    ErrorMessage = MsgBox("You messed up", vbOKCancel)
      If ErrorMessage = vbOK Then
        MiniSub1
      Else
        Exit Sub
      End If

MiniSub2
MiniSub3

End Sub

 

[EricL89]

Thanks for the quick responses guys! Just got back into work to see these posts.

JLGWhiz - If I'm not mistaken, I think End has to be followed by something like If, Sub, etc. I tried doing End Sub Master(), but it didn't work.

Rick & Osvaldo- This seems like a possibility, but it looks like I neglected to mention that MiniFunction1 contains code prior to MsgBox. The purpose of my workbook is to organize prospects, so these prospects are listed on one spreadsheet, "Master". I have a button that runs the macro, which goes down the list looking for prospects with the final column containing some form of either "yes" or "no"; prospects with yes get moved to sheet two, "Sold", and the ones with no get moved to sheet three, "Lost". The ErrorMessage only runs if a prospect has yes or no in their final column, but not all of the other cells in their row are filled. Essentially, I want this macro to only sort the prospects that have all cells filled. Hence, when the ErrorMessage pops up we can either click OK to continue sorting, or Cancel to stop the macro in order to fix the problem. Again, MiniFunction1 contains code before and after the MsgBox line. Only Minifunction1 will have this function, but it will be entered twice; once following If "yes", and again following If "no".

MiniFunction2 is similar to Minifunction1 (minus the error message), except it runs on the worksheet "Sold" returning prospects to "Master" if the yes in the final column was deleted. The same applies for Minifunction3, except it runs on "Lost".

How should the code you provided be adjusted to account for this?

Thanks again!

Eric

 

[Rick Rothstein]

Rick & Osvaldo- This seems like a possibility, but it looks like I neglected to mention that MiniFunction1 contains code prior to MsgBox. The purpose of my workbook is to organize prospects, so these prospects are listed on one spreadsheet, "Master". I have a button that runs the macro, which goes down the list looking for prospects with the final column containing some form of either "yes" or "no"; prospects with yes get moved to sheet two, "Sold", and the ones with no get moved to sheet three, "Lost". The ErrorMessage only runs if a prospect has yes or no in their final column, but not all of the other cells in their row are filled. Essentially, I want this macro to only sort the prospects that have all cells filled. Hence, when the ErrorMessage pops up we can either click OK to continue sorting, or Cancel to stop the macro in order to fix the problem. Again, MiniFunction1 contains code before and after the MsgBox line. Only Minifunction1 will have this function, but it will be entered twice; once following If "yes", and again following If "no".

MiniFunction2 is similar to Minifunction1 (minus the error message), except it runs on the worksheet "Sold" returning prospects to "Master" if the yes in the final column was deleted. The same applies for Minifunction3, except it runs on "Lost".

How should the code you provided be adjusted to account for this?

Sorry if I gave you the wrong idea, but you can have code before the MsgBox... no problem. And that code can do physical things to your workbook (as long as you call the function from within the VB environment and not use it on a worksheet as a UDF... user defined function)... again, no problem. The only difference between a Sub and a Function is that a function returns a value an a Sub doesn't... all your existing code for MiniSub1, MiniSub2, etc. can remain the same... just convert the keyword Sub to Function (and rename the procedure if you agree with me that the name should be accurate, otherwise leave the procedure names alone) and and the return value structure I posted earlier.

 

[EricL89]

Okay, I tried following what you showed in your first post, Rick, but it's giving me difficulty. It said I couldn't have "Exit Sub" inside Function. This is what I've got:

(Note: What I used here = in my code......... Sub Master() = Sub CopyToOtherSheet() ; Sub MiniSub1() = Sub EditMaster() ; Sub MiniSub2() = Sub EditSold() ; Sub MiniSub3() = Sub EditLost()...... My three worksheets are labeled: Master, Sold, & Lost.)

Option Explicit

Sub CopyToOtherSheet()


   If EditMaster Then Exit Sub
   EditSold
   EditLost


ThisWorkbook.Sheets("Master").Activate


Range("a4").Select


MsgBox ("Prospect Organization Complete!")


End Sub






Function EditMaster() As Boolean


Dim winorlose As String
Dim lrsold As Long
Dim lrlost As Long
Dim ErrorMessage As String


Application.ScreenUpdating = False


ThisWorkbook.Sheets("Master").Activate


Range("a4").Select


Do Until ActiveCell = Range("a203")


lrsold = ThisWorkbook.Sheets("Sold").Cells(Rows.Count, 1).End(xlUp).Row + 1
lrlost = ThisWorkbook.Sheets("Lost").Cells(Rows.Count, 1).End(xlUp).Row + 1


winorlose = ActiveCell.Offset(0, 9).Value


If winorlose = "Y" Or winorlose = "y" Or winorlose = "Yes" Or winorlose = "YES" Or winorlose = "yes" Then


If IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty(ActiveCell.Offset(0, 2)) Or IsEmpty(ActiveCell.Offset(0, 3)) Or IsEmpty(ActiveCell.Offset(0, 4)) Or IsEmpty(ActiveCell.Offset(0, 5)) Or IsEmpty(ActiveCell.Offset(0, 6)) Or IsEmpty(ActiveCell.Offset(0, 7)) Or IsEmpty(ActiveCell.Offset(0, 8)) Then


    ErrorMessage = MsgBox("The prospect """ & ActiveCell & """ contains empty cells.  No prospects will be moved unless all cells are filled.", vbOKCancel)
    
    If ErrorMessage = vbOK Then
    
    ActiveCell.Offset(1, 0).Select
    
    Else
        EditMaster
        Exit Sub
    End If
    Exit Function
    
Else


    ActiveCell.EntireRow.Copy
    ThisWorkbook.Sheets("Sold").Activate
    Cells(lrsold, 1).Select
    ActiveCell.PasteSpecial
    Range("A" & lrsold).Select
    ThisWorkbook.Sheets("Master").Select
    ActiveCell.EntireRow.Delete


End If


Else


If winorlose = "N" Or winorlose = "n" Or winorlose = "No" Or winorlose = "NO" Or winorlose = "no" Then


If IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty(ActiveCell.Offset(0, 2)) Or IsEmpty(ActiveCell.Offset(0, 3)) Or IsEmpty(ActiveCell.Offset(0, 4)) Or IsEmpty(ActiveCell.Offset(0, 5)) Or IsEmpty(ActiveCell.Offset(0, 6)) Or IsEmpty(ActiveCell.Offset(0, 7)) Or IsEmpty(ActiveCell.Offset(0, 8)) Then


    ErrorMessage = MsgBox("The prospect """ & ActiveCell & """ contains empty cells.  No prospects will be moved unless all cells are filled.", vbOKCancel)
    
    If ErrorMessage = vbOK Then
    
    ActiveCell.Offset(1, 0).Select
    
    Else
    
    Exit Function
    
    End If
    
Else


    ActiveCell.EntireRow.Copy
    ThisWorkbook.Sheets("Lost").Activate
    Cells(lrlost, 1).Select
    ActiveCell.PasteSpecial
    Range("A" & lrlost).Select
    ThisWorkbook.Sheets("Master").Select
    ActiveCell.EntireRow.Delete
    
End If


Else


    ActiveCell.Offset(1, 0).Select
    
End If


End If
Loop




Application.CutCopyMode = False


Application.ScreenUpdating = True






End Function

The subs EditSold and EditLost come after the rest of this, but have no need for changes; if the macro makes it to EditSold, it will proceed to EditLost then finish CopyToOtherSheet without issue. I just want EditMaster to terminate CopyToOtherSheet if "Cancel" is clicked for the message box in either the If Yes or If No error messages.

Think you can lend me a hand with fixing up this code? Sorry if it seems a little messy, just working with what I know...

 

[Rick Rothstein]

Okay, I tried following what you showed in your first post, Rick, but it's giving me difficulty. It said I couldn't have "Exit Sub" inside Function.

Sorry, my fault... I forgot to mention that everything must be consistent... if you change the procedure from a Sub to a Function, then you must change "Exit Sub" to "Exit Function". I haven't looked at the rest of your code... I'll wait to see if you have any further problems after making the above change.

 

[EricL89]

Sorry, my fault... I forgot to mention that everything must be consistent... if you change the procedure from a Sub to a Function, then you must change "Exit Sub" to "Exit Function". I haven't looked at the rest of your code... I'll wait to see if you have any further problems after making the above change.

Even changing that, because "EditMaster" is before that Exit Function (previously Exit Sub) it just becomes a constant loop. Removing that "EditMaster" line makes the macro proceed to the next sub, EditSold, which I don't want it to do. I just tried adding another sub (and tried function as well), which I named "Terminate". I changed the If statement so that if cancel was clicked, it would go to Terminate. I placed Terminate after EditLost in the main sub, hoping that by clicking cancel and sending the macro to Terminate, it would skip EditSold and EditLost. Instead, after running Terminate it proceeds to EditSold.

I also tried putting Terminate between EditMaster and EditSold, but that didn't work either. Any ideas?

 

[EricL89]

I figured it out. In the main sub, the statement needed to be:

If Not EditMaster Then Exit Sub

That seems to have resolved the issue for now. I'm going to play around with it a little more and make sure there aren't any bugs.

Thanks for your help!