Run-time error '1004' Save Cancel???????!!!!!!!!!!1

[davidgottlieb]

Private Sub btnSave_Click()
Dim ctrlno As String
Dim filename As String
ctrlno = TextBox1.Text
MsgboxResult = MsgBox("Do you already have a referral folder created on your computer?", vbYesNo, "Retail Investment Sales Referral Form")
If MsgboxResult = vbYes Then
    existingfolder = InputBox("Please enter the name of the folder in which your referral files are contained:", "Retail Investment Sales Referral Form")
    existingfoldercheck = "c:\" & existingfolder
    If Dir(existingfoldercheck, vbDirectory) <> "" Then
        filename = existingfoldercheck & "\" & ctrlno & ".xls"
        
            ActiveWorkbook.SaveAs filename:= _
            filename, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
    End If
Else
    If MsgboxResult = vbNo Then
    Dim InputBoxResult As String

    InputBoxResult = InputBox("Please enter a name for the folder to which you will be saving your referrals in:")
    NewDir = "C:\" & InputBoxResult
    'ChDir "C:\"
    MkDir (NewDir)
    NewDirMsg = "Your referrals will be saved in:" & NewDir
    MsgBox NewDirMsg
    filename = NewDir & "\" & ctrlno & ".xls"
        ActiveWorkbook.SaveAs filename:= _
            filename, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
    End If
End If
End Sub

Say for instance the file tries to save over itself and it asks the user "as;lfk already exists, would you like to overwrite the file?" They click "Cancel". Well, when they click cancel it comes up with an error:

Run-time error '1004'
Method 'SaveAs' of object '_Workbook' failed.

How do you keep that from happening?

[Lafor]

Have an ON ERROR GOTO Statement to trap the error
Display the error number
Once u know the error number
u can do something like

in your module or something
Const CANCEL_KEY_OFF_CONNECTION_SCREEN = 2147217842

and in your error handler something like this

if error number is CANCEL_KEY_OFF_CONNECTION_SCREEN
then do nothing

I am in a rush...

but does that make sense?

[davidgottlieb]

I am a beginner. Could you go into a little more detail??
Thank you so much for your help.

Use On Error Resume Next if you want to ignore the error.

And here is how to capture the Error:

'Private Sub ....()
On Errore Goto ErrorTrap
'code
Exit Sub
ErrorTrap:
Msgbox "Error:  " & Err.Number & " - " & Err.Description
'End Sub

[Joacim Andersson]

You can use error trapping with the On Error statement.
On Error can be used in two ways;
Either it jumps to an other part of your procedure where you have all error handling code (On Error Goto LabelName).
Or it could just ignore the error and resume with the next line of code (On Error Resume Next).

If you have error handling code with the On Error statement in a procedure you can turn it off with a call to 0 (On Error Goto 0).

You can use the Err object to check what error has accored. The Err object has properties such as Value and Description.
The Err object can also be used to raise errors (Err.Raise), this is used when you create your own components, such as classes, ActiveX Controls and ActiveX DLLs.

If you call a API function (DLL calls declared with the Declare keyword) they don't raise any errors but usually just returns a value that indicate that the function has failed.
VB then stores the actual error in the LastDLLError property of the Err object even if you don't use any other error handling code.

In your case, as mentioned in previous posts, I would go with an On Error Resume Next statement to simply ignore any error that may occure in your procedure.
You can add that line of code at the start of the sub.

Private Sub btnSave_Click()
Dim ctrlno As String
Dim filename As String

On Error Resume Next 'ignore any errors in this procedure
ctrlno = TextBox1.Text
MsgboxResult = MsgBox("Do you already have a referral ...?", _
  vbYesNo, "Retail Investment Sales Referral Form")
If MsgboxResult = vbYes Then
    '...the rest of the code´

Best regards and good luck!