Error Handling Exit Sub

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
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)

VBA Code:
'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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,941
Office Version
  1. 365
Platform
  1. Windows
Add Exit Sub to the code right before you enter the error handling section.
 

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
'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

Well-known Member
Joined
Apr 28, 2004
Messages
75,941
Office Version
  1. 365
Platform
  1. Windows
The error handling code should be at the end of the sub not in the middle of the code.
 

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,409
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,941
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Something like this perhaps.
VBA Code:
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

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
Norie - I think that will work!! I'll test it and get back to you.
 

katekoz

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
Michael M -
Here is the full macro:

VBA Code:
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

Board Regular
Joined
Jan 20, 2020
Messages
91
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
'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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,539
Messages
5,548,631
Members
410,861
Latest member
Victor96
Top