Error Handling Exit Sub

[katekoz]

Hello,
I have an OnError GoTo statement. If there is an error with the MkDir (aka, the file already exists), then I want it to go to the ErrMsg that asks if the user wants to continue or not. If they continue, the files will overwrite. If they don't, the macro ends. This is working perfectly right now. The problem I have is when there is NO error. If that's the case, then the file gets created as expected, but then it sends the ErrMsg anyway. I don't want it to send the error message when there is no error. How do I fix this?

(side note: when the error exists, I want it to show the ErrMsg, NOT exit the sub)

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

On Error GoTo ErrMsg

MkDir "\\test.test.net\sites\departments\test\Test\Test Test\Test Test\" & strDirname

ErrMsg: ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
    Select Case ans
        Case vbYes
            ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
        Case vbNo
            MsgBox ("Files were not saved.")
            Exit Sub
        Case vbCancel
            MsgBox ("Files were not saved.")
            Exit Sub
    End Select

'Continue on with more code from here

 

[Norie]

Add Exit Sub to the code right before you enter the error handling section.

 

[katekoz]

Hello - thanks for your response! I had that entered earlier, but what happens is the following:

Enter macro
MsgBox "Please wait..."
Folder gets created
Macro Exited

The macro won't continue on to the next command after the error message - it just exits. I need it to continue, but to skip the message and continue.

Here is the code as you suggest, when it just exits the macro after the folder is created.

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

On Error GoTo ErrMsg

MkDir "\\test.test.net\sites\departments\test\Test\Test Test\Test Test\" & strDirname
Exit Sub

ErrMsg: ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
    Select Case ans
        Case vbYes
            ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
        Case vbNo
            MsgBox ("Files were not saved.")
            Exit Sub
        Case vbCancel
            MsgBox ("Files were not saved.")
            Exit Sub
    End Select

'Continue on with next step of code.

 

[Norie]

The error handling code should be at the end of the sub not in the middle of the code.

 

[katekoz]

I can't put it at the end because I need to give the user the ability to override the error and continue with the macro despite it. Perhaps there's something I should use other than On Error GoTo? Maybe just a regular If Then statement? I'm not sure how to do an If Then with an error as a trigger, though. Any ideas?

 

[Michael M]

Maybe you should post ALL of the code so we can see what it does....The Exit Sub may not be needed so many times !!

 

[Norie]

You could add code to check if the folder exists rather then using On Error...

Something like this perhaps.

If Len(Dir("\\test.test.net\sites\departments\test\Test\Test Test\Test Test\" & strDirname, vbDirectory)) > 0 Then

    ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")

    If ans = vbYes Then
        ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
    Else
        Exit Sub
    End If

End If

 

[katekoz]

Norie - I think that will work!! I'll test it and get back to you.

 

[katekoz]

Michael M -
Here is the full macro:

Sub PDF_User_Graphs()
'

Dim ws As Worksheet
Dim sourcesheet As Worksheet
Dim strDirname As String
Dim strPathname As String
Dim strDefpath As String

Set sourcesheet = ActiveSheet

Application.ScreenUpdating = False

MsgBox "Please wait while the folder and files are created and saved.  This will take ~60 seconds.", , "Complete"

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

On Error GoTo ErrMsg

MkDir "\\test.test.net\sites\departments\test\" & strDirname
Exit Sub

ErrMsg: ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
    Select Case ans
        Case vbYes
            ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue
        Case vbNo
            MsgBox ("Files were not saved.")
            Exit Sub
        Case vbCancel
            MsgBox ("Files were not saved.")
            Exit Sub
    End Select


'Selects all of the username worksheets (excluding the list below) and saves them as pdf's to the filepath below.
For Each ws In Worksheets
    
    If ws.name <> "All Users Month" And ws.name <> "All Users Rolling"  Then

        ws.Select
        sName = ws.name
        sDate = Format(Sheets("Inputs").Range("B2"), " MMM-yy")
        
            Range("AT65:BF103").Select

            Selection.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="\\test.test.net\sites\departments\test\" & strDirname & "\" & sName & sDate & ".pdf"
    
    End If
    
Next ws


'Selects the two sheets below and saves them as pdf's to the filepath below.
Sheets(Array("All Users Month Results", "All Users Rolling")).Select
    
ssName = "All User Results"
    
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="\\test.test.net\sites\departments\test\" & strDirname & "\" & "1 " & ssName & sDate & ".pdf"

MsgBox "The files have been created and saved.", , "Complete"

Application.ScreenUpdating = True

Call sourcesheet.Activate
    
End Sub

 

[katekoz]

Norie - That almost works. I rearranged the If/Then statement a bit to do what I need. I still have the problem of overwriting the files, though. I want the user to be able to select "Yes" and overwrite the existing file, not simply error out. Is there a way I can write into the macro to overwrite the file in the case of "Yes"? Maybe have it delete the existing folder, then run the MkDir line after?

'Create new month folder

strDirname = Format(Sheets("Inputs").Range("B2"), "yyyy-MM") & " Claims Prod Metrics" ' New folder name

If Len(Dir("\\test.test.net\sites\departments\test\" & strDirname, vbDirectory)) > 0 Then

    ans = MsgBox("This file already exists.  Would you like overwrite the existing set of PDFs for the month/year that you generated?", vbYesNoCancel, "DUPLICATE FILE")
        Select Case ans
            Case vbYes
                MkDir "\\test.test.net\sites\departments\test\" & strDirname 'This errors, but I need it to overwrite the existing folder instead.
            Case vbNo
                MsgBox ("Files were not saved.")
                Exit Sub
            Case vbCancel
                MsgBox ("Files were not saved.")
                Exit Sub
        End Select
Else: MkDir "\\test.test.net\sites\departments\test\" & strDirname
End If

ThisWorkbook.Sheets("Inputs").Range("M2").Value = UserValue

'More code here... see above post.