Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Exit Macro Without Debug Prompting
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Exit Macro Without Debug Prompting
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 -- Regards, OssieMac "GEdwards" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Exit Macro Without Debug Prompting
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Exit Macro Without Debug Prompting
Many thanks for your assistance. This is the code I needed and it works great.
"JLatham" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To Exit Macro Without Debug Prompting
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. "OssieMac" wrote: 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 -- Regards, OssieMac "GEdwards" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrict Prompting for Macro Security | Excel Programming | |||
Excel Macro Prompting | Excel Programming | |||
Macro Examples for prompting info | Excel Programming | |||
Prompting 'Save As' in an Excel Macro | Excel Programming | |||
Prompting a macro to run on change of cell content | Excel Programming |