On Error only works once?

[Skychick]

I still haven't found out why my macro will perform my On Error routine the first time an error occurs but not the second time.

I am having a macro save a workbook with a name that includes a date from one of the sheets.

If the File name already exists, the user gets the usual prompt: File already exists, replace?

If they say No, my On Error routine works and gives them a textbox to enter in a new name. Then I do a GoTo SaveFile to try and save the new file name.

If the name they enter already exists, an error occurs but is not trapped for some reason with the On Error code, even though it is the same error that hit the trap the first time. Do I need to do something to re-set this when I call the same section again? See code below:

Application.DisplayAlerts = True
Sheets("Master Data").Select
FileMM = Cells(1, 4).Value
FileDD = Cells(1, 5).Value
FileYY = Cells(1, 6).Value

Dim sFilename As String
sFilename = "G:\Payroll\Manual Batch Sheet Macros\Manual Batch Sheet Test " & FileMM & "-" & FileDD & "-" & FileYY & ".xls"

SaveSheet:

On Error GoTo Newname
ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Exit Sub

Newname:

NewFilename = Application.InputBox("Please enter a new name for this file. Example: Manual Batch Sheet 01-01-06")
If NewFilename = False Or NewFilename = "" Then
ActiveWorkbook.Close False
Exit Sub
Else
sFilename = "G:\Payroll\Manual Batch Sheet Macros\" & NewFilename & ".xls"
GoTo SaveSheet
End If

End Sub

 

[tusharm]

I know a lot of people including some regulars prefer this kind of error handler. While the structure obviously appeals to many, it is not the easiest to program. Personally, I don't like the bouncing around with the GoTos.

From the help file, "If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. "

In your case *try* -- and please test this -- Resume SaveSheet instead of the GoTo SaveSheet.

Personally, I would have programmed this with a On error resume next and a loop. In pseudo code

alldone=false
do
    on error resume next
    save...
    if error... then
        'whatever
        optionally set AllDone to true
    else
        alldone=true
    on error goto 0
    loop until alldone

vb.net introduces a more structured error handler loosely structured along the foll. lines

try {
   ...
   }
catch {
    }
finally {
    }

The closest way to duplicate this in VBA is

     On Error GoTo Catch1
    'blah, blah, blah
    GoTo AtEnd1
Catch1:
    'blah, blah, blah
    GoTo AtEnd1
AtEnd1:
    On Error GoTo 0
    'blah, blah, blah
    ...

Note that while this uses GoTos, they are the only way to implement a structured error handler. The labels are meant to serve as targets *only* for the GOTOs required to implement the structured error handler. Since no other GOTO may target those labels, one can easily verify the procedure's structural integrity. Just count the occurences of 'GoTo Catch1' and 'GoTo AtEnd1' statements. They should number 1 and 2 respectively. Any more -- or less -- and the structure is flawed.

 

[Legacy 98055]

This might work in your case as well. Use the Err object's Clear method to reset the handler.

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  Err.Clear
  <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Newname
  ActiveWorkbook.SaveAs Filename:=sFilename, FileFormat:= _
  xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
  , CreateBackup:=False
</FONT></td></tr></table>

Tom

 

[Skychick]

The Resume command worked perfectly. Thank You so much!!!!

I agree, there are much better ways this could have been coded and the loop would be a lot more concise.

I tried the suggestion in the other reply (Err.Clear) just to see if it worked but it did not. The error was still not trapped on the second occurrence.