How To Exit Macro Without Debug Prompting

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
 
O

OssieMac

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
 
J

JLatham

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
 
G

GEdwards

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
 
G

GEdwards

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top