How To Exit Macro Without Debug Prompting

  • Thread starter Thread starter GEdwards
  • Start date Start date
G

GEdwards

I am using a macro to save an XLS however if the file already exists you are
promted, as normal, if you want to replace the file or not (Yes or No). If I
choose "No" I just want to exit the macro however I am prompted with
"Continue, End or Debug".

How can I just exit from this macro gracefully? Turning "Display Alerts"
off is not an option, although it works. My code follows...

Sub SaveXLS()
If Range("A1").Value = "" Then
MsgBox ("Cell A1 must have an entry.")
Else
SaveAsFile = ThisWorkbook.Worksheets("Sheet1").Range("A1")

ChDir "E:\Fun With Excel"

'It is at this next process I have my issue...

ActiveWorkbook.SaveAs Filename:= _
"E:\Fun With Excel\Playing With Excel (" & SaveAsFile & ").xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
 
The problem is not with the Display Alerts; You have syntax errors in the
line of code.

You need the last backslash between the path and filename and remove the
parenthesis.

ActiveWorkbook.SaveAs Filename:= _
"E:\Fun With Excel\Playing With Excel\" & SaveAsFile & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


If you want to save over an existing file and don't want the warning alert
then include the following line before the above code.
Application.DisplayAlerts = False
 
Since you said that turning display alerts off is not an option. And if you
want it to be completely "quiet", delete my MsgBox line of code.

Sub SaveXLS()
If Range("A1").Value = "" Then
MsgBox ("Cell A1 must have an entry.")
Else
SaveAsFile = ThisWorkbook.Worksheets("Sheet1").Range("A1")

ChDir "E:\Fun With Excel"

'It is at this next process I have my issue...
On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
"E:\Fun With Excel\Playing With Excel (" & SaveAsFile & ").xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
If Err <> 0 Then
'probably clicked [NO] in response to overwrite warning
'so just ignore the error and press on
MsgBox "Error: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
"Encountered. File NOT saved.", vbOKOnly + vbCritical, "Error
Encountered"
Err.Clear
End If
On Error GoTo 0 ' clear error trapping
End If
End Sub
 
Many thanks for your assistance. This is the code I needed and it works great.

JLatham said:
Since you said that turning display alerts off is not an option. And if you
want it to be completely "quiet", delete my MsgBox line of code.

Sub SaveXLS()
If Range("A1").Value = "" Then
MsgBox ("Cell A1 must have an entry.")
Else
SaveAsFile = ThisWorkbook.Worksheets("Sheet1").Range("A1")

ChDir "E:\Fun With Excel"

'It is at this next process I have my issue...
On Error Resume Next
ActiveWorkbook.SaveAs Filename:= _
"E:\Fun With Excel\Playing With Excel (" & SaveAsFile & ").xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
If Err <> 0 Then
'probably clicked [NO] in response to overwrite warning
'so just ignore the error and press on
MsgBox "Error: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
"Encountered. File NOT saved.", vbOKOnly + vbCritical, "Error
Encountered"
Err.Clear
End If
On Error GoTo 0 ' clear error trapping
End If
End Sub


GEdwards said:
I am using a macro to save an XLS however if the file already exists you are
promted, as normal, if you want to replace the file or not (Yes or No). If I
choose "No" I just want to exit the macro however I am prompted with
"Continue, End or Debug".

How can I just exit from this macro gracefully? Turning "Display Alerts"
off is not an option, although it works. My code follows...

Sub SaveXLS()
If Range("A1").Value = "" Then
MsgBox ("Cell A1 must have an entry.")
Else
SaveAsFile = ThisWorkbook.Worksheets("Sheet1").Range("A1")

ChDir "E:\Fun With Excel"

'It is at this next process I have my issue...

ActiveWorkbook.SaveAs Filename:= _
"E:\Fun With Excel\Playing With Excel (" & SaveAsFile & ").xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub
Click to expand...
Click to expand...
 
OssieMac,
Thanks for your review of my code. My save was coded correctly as I did
want the value "SaveAsFile" as part of the XLS filename. For example;

E:\Fun With Excel\Playing With Excel (Macro Fun).xls

The problem was if I clicked [NO] or [Cancel] in response to the overwrite
warning I received the error, although not really an error.

See the example from JLatham.

Thanks again.
 
Back
Top