Ending macro early

**TedH** · 01-20-2009, 12:45 AM

I need to step out of a macro based on a user respone, and I don't know how to end this macro if the user needs to correct a cell entry.

If they respond "no", I want to sub to end and select the cell where the correction needs to be made.

Thanks
Ted

If Dir(filePath, vbDirectory) = vbNullString Then
        Dim FolderMsg, ResponseFolder
        FolderMsg = "This folder does not exist." & Chr(13) & Chr(13) _
           & "Do you want to create a new customer folder?"
       ResponseFolder = MsgBox(FolderMsg, vbYesNo + vbInformation)
If ResponseFolder = vbYes Then
             MkDir filePath
End If
If ResponseClose = vbNo Then
            Dim CustMsg, ResponseCustMsg As String
            CustMsg = "Check the customer name." & Chr(13) & Chr(13) _
            & "Correct if needed."
            ResponseCustMsg = MsgBox(CustMsg, vbOKOnly + vbCritical)
            Range("I5").Select
            Exit Sub  'THIS IS WHERE I NEED TO EXIT THE MACRO
End If

End If
    Rem
    Rem http://www.excelforum.com/excel-programming/663085-save-as-and-creating-directory.html
    Rem
    Rem Save the file in the proper folder
If ActiveWorkbook.FullName = filePath & Application.PathSeparator & namePN & " PrtRev_ " _
                         & nameRev & " SO_" & nameSO & " ILRev2.xls" Then
    Dim DuplicateMsg, ResponseDuplicate
    DuplicateMsg = "This file already exists." & Chr(13) & Chr(13) _
        & "Do you want to overwrite it?"
    ResponseDuplicate = MsgBox(DuplicateMsg, vbYesNo + vbInformation)
If ResponseDuplicate = vbYes Then
    ActiveWorkbook.Save
        
End If
Else
ActiveWorkbook.SaveAs filePath & Application.PathSeparator & namePN & " PrtRev_ " _
                         & nameRev & " SO_" & nameSO & " ILRev2.xls"
Range("e1") = "serenity now!"
End If
    Rem
    ActiveWindow.WindowState = xlMaximized
    Calculate
    Range("F13").Select
    Rem ActiveWorkbook.Close
    
    Rem End of loop should go here
    
    Rem Dim CloseMsg, ResponseClose
    Rem PrintMsg = "Do you want to close the file?" & Chr(13)
       Rem ResponseClose = MsgBox(PrintMsg, vbYesNo + vbInformation)
    Rem If ResponseClose = vbYes Then
    Rem ActiveWorkbook.Close True
    Rem End If

Else
    Dim WrongMsg As String
    WrongMsg = "This may not be an Inspectional Layout file." & Chr(13) & Chr(13) _
    & "This macro will not run on files that are not this type."
    MsgBox WrongMsg, vbOKOnly + vbCritical
   
End If
'

End Sub

**jasoncw** · 01-20-2009, 12:46 AM

Hi Ted. I do not see your problem. By using Exit Sub, you are exiting the macro.

**TedH** · 01-20-2009, 12:56 AM

I have a bunch of code after this. If I run it as is, it continues to run the following code....

I'll paste the code that follows in the original post.
Ted

**jasoncw** · 01-20-2009, 01:07 AM

Hi Ted.

Do you receive the message box "Check the customer name. Correct if needed."? If you do, based on what you have above, the procedure should end after you click OK.

**shg** · 01-20-2009, 01:49 AM

If you need to know if a sub completes successfully, then change it to a function that returns a Boolean, and test it in the calling sub.

**TedH** · 01-20-2009, 10:07 AM

jasoncw: I get the "check customer" msgbox; clicking yes there are no problems. On clicking "no", the end sub isn't doing it's thing; the macro continues to run because I'm getting a runtime error on the saveas function a little further down.

shg: I'm still a noob; I usually throw a dummy expression into the worksheet to see if the code works. {i.e. "serenity now!"}