VBA .Saveas Error 1004 When No or Cancel

[Tommy Random]

Hi Team,

I am using the following code to save a file with a new name

Private Sub CommandButton1_Click()
CommandButton1.BackColor = 5950882
ActiveWorkbook.SaveAs (Range("d4").Value & ".xlsm")


End Sub

When there is no existing file with the same name, the file is saved as desired.

When there is an existing file with the same name, a popup arises notifying the user as follows:

A file called 'File Name Specified in cell d4.xlsm' already exists in this location. Do you want to replace it?

If the user selects "Yes", no problem - the file is overwritten.

If the user selects "No", the following popup arises notifying the user as follows:

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

I would like to modify the above code so that if the user selects "No", the VBA code would terminate without any further popup notification to the user.
(I still want the user the option to overwrite an existing file. So I do not wish to preempt all error handling).

I tried inserting the following line into the above code, but it did not help:

If Filename = "False" Then Exit Sub



Any suggestions?

Thank you.

 

[JoeMo]

Can you show us the code that generates the pop-up if the user specifies a file name that already exists? It's hard to help w/o seeing all your code.

 

[Tommy Random]

Thank you JoeMo for your interest in this question.

The code given is the code I am using in its entirety.

If a file name already exists, Excel itself generates the popup message:

A file called 'File Name Specified in cell d4.xlsm' already exists in this location. Do you want to replace it? (Yes, No, and Cancel options are given.)

Up to the point where that message is generated, all is well as I wish the user to retain the option to decline overwriting a file.

When the user selects "No" (or "Cancel") Excel generates another popup message (which is the one I would like to eliminate or bypass).

 

[JoeMo]

Exactly what is in cell D4?

 

[Tommy Random]

Hi Joe,

Cell D4 contains this formula: ="Assignments "&D1&" "&D2
Cells D1 and D2 contain text, a students first name in D1 and a students last name in D2.
So the value in Cell D4, for example, would be (text) "Assignments Tommy Random"

Thanks.

 

[JoeMo]

Try putting Application.DisplayAlerts = False just before the SaveAs line.

 

[Tommy Random]

Hi Joe,

Thank you for taking an interest in my question.

This code solves the problem:

 Private Sub CommandButton1_Click()CommandButton1.BackColor = 5950882

On Error Resume Next
ActiveWorkbook.SaveAs (Range("d4").Value & ".xlsm")
On Error GoTo 0


End Sub

I consider this question resolved.

 

[Tommy Random]

I wanted to edit my last post but couldn't remember how to do so.

I found that Application.DisplayAlerts = False
does not allow the user to choose whether or not to overwrite an existing file with the same name. Instead, the file is overwritten without warning.

Again. Thank you for your interest in my question.