Question I have a save as macro defined to operate when someone selects a specific text box in the Excel window. The macro runs smoothly when the user selects OK, however, if the user selects "cancel" or "no" the macro creates a VB error message. Is there a code that can be added to my code to stop this error from occurring. My code is below:
Thanks
Sub SelectSaveFileName()
Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename("C:\BPMT.xls")
ActiveWorkbook.SaveAs Filename:= _
"C:\BPMT.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
Answer Micah,
If user select No or Cancel VB Err 1004 will raised. You have to decide what will happen after this error occurred. Here is modified code:
Sub SelectSaveFileName()
On Error goto Err_No_Cancel
Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename("C:BPMT.xls")
ActiveWorkbook.SaveAs Filename:= _
"C:BPMT.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Err_No_Cancel:
if err.number = 1004 Then
' Your code here
' Msgbox "User select No or Cancel."
end if
