Expert: Subbu - 6/1/2009

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       

End Sub


Let me know if you have any issue.

Subbu.