JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
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.
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).
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.
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.
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.
