Error Trapping on Cancel

[03856me]

I have written code to export two tabs and rename the file and do other manipliation to the data which works fine as long as the user chooses Yes or No to the messages, but if they choose cancel after the first Yes, an error comes up. My Yes and No responses are working correctly, just the cancel option that errors out. Can someone help me modify this code to take care of that? or point me where to look.

I am getting a 1004 error at the where indicated below with 'XXXXX and it doesn't close the new file.

Your help is greatly appreciated

'================================================
' THiS MACRO DUMPS THE EDIT FILE DATA AND THE TRACT INFO 
 
Sub DumpEdit()
If Range("TractName").Value = "" Then
    MsgBox ("Missing File Name - Add through Enter/Edit Tract Info button")
 
Else
If MsgBox("CAUTION:  Do you want to create the Export File now?", vbQuestion + vbYesNo) = vbYes Then
    Application.ScreenUpdating = False
    ActiveSheet.DisplayPageBreaks = False
    Application.StatusBar = "Working....."
 
'Copies data and hold tabs out of original workbook to a new workbook
Dim DataWorkbook As Workbook
Set DataWorkbook = ActiveWorkbook
    Worksheets("data").Visible = True
    Worksheets("Hold").Visible = True
    DataWorkbook.Sheets(Array("data", "HOLD")).Copy
ChDir "C:\Cruize-New"
On Error GoTo myerror
ActiveWorkbook.SaveAs FileName:="C:\Cruize-New\" & Range("TractName") & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled
If ActiveWorkbook.Saved = True Then
    ProcessNewFile
    Worksheets("data").Visible = False
    Worksheets("Hold").Visible = False
Application.ScreenUpdating = True
End If
End If
    Application.StatusBar = False
Exit Sub
myerror:
    If Err.Number = 1004 Then
    Workbooks(2).Activate
    ActiveWorkbook.Close
    End If
    End If
 
              Worksheets("data").Visible = xlSheetVeryHidden
'XXXXX    Worksheets("Hold").Visible = xlSheetVeryHidden
 
    MsgBox "Your data WAS NOT exported to a new file!"
End Sub
'===============================================
Sub ProcessNewFile()
    Application.ScreenUpdating = False
    Sheets("data").Unprotect Password:="test"
Worksheets("data").Select
    ActiveSheet.Cells.EntireColumn.Hidden = False
    ActiveSheet.Cells.EntireRow.Hidden = False
 
    Range("AB:KN").Delete
    Range("Z:Z").Delete
    Range("X:X").Delete
    Range("V:V").Delete
    Range("T:T").Delete
    Range("R:R").Delete
 
    Rows("6:7").Select
    Selection.EntireRow.Hidden = True
    Range("A8").Select
 
    ActiveSheet.Shapes("ReportMenu").Delete
Worksheets("Hold").Select
    Sheets("Hold").Unprotect Password:="test"
    ActiveSheet.Shapes("Rounded Rectangle 1").Delete
    Range("I:J").Delete
    Range("B9").Validation.Delete
    Range("A1:AC166").Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Save
ActiveWorkbook.Close
 
Worksheets("data").Visible = xlSheetVeryHidden
Worksheets("Hold").Visible = xlSheetVeryHidden
MsgBox "YOUR NEW REPORT HAS BEEN SAVED"
Application.ScreenUpdating = True
End Sub

 

[adam087]

Sorry if I'm being dense but I can only see one point where they can respond any they're only allowed Yes or No. Where's the prompt that gives them the option to cancel? :S

Thanks
Adam

 

[03856me]

If the code is fired and they choose Yes to "Do you want to create the Export File now?"

When they choose Yes, all is good if they file does not already exist. They can also choose No or Cancel here and everything works ok.

If the file exists, thye get the Excel message "A file named.....already exists in this location. Do you want to replace it?"

If they choose Yes, again, all is good. If they choose No or Cancel they get another message (from Excel) that says Do you want to save the changes you made to 'Book##'?.....I would like to intercept this message before they get it.

 

[Atroxell]

You can always put in a test to see if the file exists prior to creation, and if it does delete it. Or you can put a date and time stamp into the new file name, that way it will always (or nearly always) be named uniquely. That will avoid the whole situation.

 

[GTO]

If the code is fired and they choose Yes to "Do you want to create the Export File now?"

When they choose Yes, all is good if they file does not already exist. They can also choose No or Cancel here and everything works ok.

If the file exists, thye get the Excel message "A file named.....already exists in this location. Do you want to replace it?"

If they choose Yes, again, all is good. If they choose No or Cancel they get another message (from Excel) that says Do you want to save the changes you made to 'Book##'?.....I would like to intercept this message before they get it.

Hi there,

Not tested, but I think you want to do something right about where I marked IssueLine:

Again, not tested (I'm late out the door at the moment), but if you shut off Alerts for the moment, I think it will just overwrite a pre-existing file. If you wish to give the user the choice, I find it easier to test for the existing file first, rather than trying to handle Excel's responses.

I think your outermost IF is a little off, maybe:

Option Explicit
    
'================================================
' THiS MACRO DUMPS THE EDIT FILE DATA AND THE TRACT INFO
    
Sub DumpEdit()
If Range("TractName").Value = "" Then
    
    MsgBox ("Missing File Name - Add through Enter/Edit Tract Info button")
    
Else
    If MsgBox("CAUTION:  Do you want to create the Export File now?", vbQuestion + vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        ActiveSheet.DisplayPageBreaks = False
        Application.StatusBar = "Working....."
     
        'Copies data and hold tabs out of original workbook to a new workbook
        Dim DataWorkbook As Workbook
        Set DataWorkbook = ActiveWorkbook
        Worksheets("data").Visible = True
        Worksheets("Hold").Visible = True
        DataWorkbook.Sheets(Array("data", "HOLD")).Copy
        ChDir "C:\Cruize-New"
        
        On Error GoTo myerror
        
IssueLine:
        ActiveWorkbook.SaveAs Filename:="C:\Cruize-New\" & Range("TractName") & ".xlsm", _
                              FileFormat:=xlOpenXMLWorkbookMacroEnabled
        
        
        If ActiveWorkbook.Saved = True Then
            ProcessNewFile
            Worksheets("data").Visible = False
            Worksheets("Hold").Visible = False
        Application.ScreenUpdating = True
        End If
    End If
    Application.StatusBar = False
End If  '<---Outermost IF
Exit Sub
myerror:
    If Err.Number = 1004 Then
        Workbooks(2).Activate
        ActiveWorkbook.Close
    End If
'End If
 
    Worksheets("data").Visible = xlSheetVeryHidden
'XXXXX    Worksheets("Hold").Visible = xlSheetVeryHidden
    MsgBox "Your data WAS NOT exported to a new file!"
End Sub
'===============================================

Hope that helps,

Mark

 

[03856me]

SOLVED; Thanks GTO, you got me thinking and I played around with the DisplayAlerts. By placing Application.DisplayAlerts = False and True as below it works perfectly now and always overrides the existing file if they choose yes.....

.....
 
On Error GoTo myerror
 
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="C:\Cruize-New\" & Range("TractName") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
If ActiveWorkbook.Saved = True Then
...

 

[GTO]

Glad that worked out easily and thank you for the feedback