exit macro when cancel or no is selected

joelanin

New Member
Joined
Jun 3, 2012
Messages
6
im getting an excel alert message that says, "file exists.Do you want to replace it?" I want to terminate my macro program when user selects NO or CANCEL. what code shall i use?
 
Thanks for your reply.
here's my code.

ActiveWorkbook.SaveAs Filename:=myfilename
MsgBox ("File Saved.")

The above code works fine if the "myfilename" doent exists. When the filename already exists, a display allerts message pops up that says"filename exists. Do you want to replace it? if user clicks OK, the code works fine as is. But when the button NO or CANCEL is clicked, the program displays error message,,, end or debug..... I want to terminate the code when NO or CANCEL is selected, how is it done? thanks
 
Upvote 0
hi Mohammad,
Thank you for replying.
The simple code below will simulate the problem.

Sub Macro1()

Range("A1").Select
ActiveCell.FormulaR1C1 = "fdsfdsfdsfdsfds"
ActiveWorkbook.SaveAs Filename:= _
"C:\Book1.xls"
MsgBox ("File Saved.")

End Sub

I d like to get rid of the run time error when the NO or CANCEL button is selected. How is it done? Thanks
 
Upvote 0
Thank you for the quick reply.

Which of the following statements are you using to save the workbook?
Code:
[FONT="Consolas"][SIZE="2"][COLOR="Navy"]ActiveWorkbook.SaveAs Filename:=myfilename[FONT="Verdana"][COLOR="Black"]

Or[/COLOR][/FONT]

ActiveWorkbook.SaveAs Filename:= "C:\Book1.xls"[/COLOR][/SIZE][/FONT]

If it is the first, what is the value of myfilename? In other words, does it include the path or just the name?
 
Upvote 0
I d like to get rid of the run time error when the NO or CANCEL button is selected. How is it done? Thanks

Maybe like this:

Code:
Sub Macro1()

On Error GoTo ErrorHandle
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Book1.xls"
    MsgBox ("File Saved.")

ExitRoutine:
    Exit Sub

ErrorHandle:
    MsgBox "File not saved."
    Resume ExitRoutine

End Sub
 
Upvote 0
You need to check yourself if the file exists.
Code:
If Dir("C:\Book1.xls") <> "" Then
 
       Resp = Msgbox("File exists. Do you want to overwrite it?", vbYesNoCancel)
 
       ' check for No or Cancel
       If Resp <> vbYes Then Exit Sub
End If
 
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
    "C:\Book1.xls"
    MsgBox ("File Saved.")
Application.DisplayAlerts = True
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top