How to enable user to click "cancel" if file name exists when saving as...

[TBO]

I have a command button that saves my workbook with a new name generated from two different cells. I have looked over many threads and can't quite figure out how to solve my problem: when a user enters a name that already exists and clicks the button, it tells them that the file name exists and they can choose whether or not to replace it. If they click "cancel" or "no" then they receive an error. Can someone help me find a way to enable them to choose no or cancel without getting this error?

Here is the code I have so far:

Private Sub CommandButton1_Click()
       
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Range("L4") & " " & Range("N4"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    Range("A8:F308").Select
    Selection.ClearContents

ThisWorkbook.Save
    
End Sub

It works wonderfully except for when the user accidentally saves it when the file name already exists and they choose to cancel the action. Any help would be appreciated!

Thanks in advance

[patel45]

If I understand well

Private Sub CommandButton1_Click()
    Application.DisplayAlerts = False       
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Range("L4") & " " & Range("N4"),_ FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Application.DisplayAlerts = True
    Range("A8:F308").Select
    Selection.ClearContents

ThisWorkbook.Save
    
End Sub

[HaHoBe]

Hi, TBO,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[TBO]

Sorry, I am still new to this Forum...

Yes that code works great, Patel45, as long as they are ok with replacing the existing file. I would still like them to be able to have the option to click the cancel button if they didn't realize they were saving over another file.

[patel45]

Private Sub CommandButton1_Click()
    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Range("L4") & " " & Range("N4"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    Range("A8:F308").Select
    Selection.ClearContents

ThisWorkbook.Save
    
End Sub

[TBO]

Thanks, that gave me a head start as to where I needed to go to get what I wanted. Here is the sub routine that ended up working for me. Thanks again!

Private Sub CommandButton1_Click()

    On Error GoTo ErrorHandler

    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & Range("L4") & " " & Range("N4"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    Range("A8:F308").Select
    Selection.ClearContents

ThisWorkbook.Save

ErrorHandler: Exit Sub
    
End Sub

[HaHoBe]

Hi, TBO,

could you be kind enough to explain the diference between ActivbeWorkbook and ThisWorkbook to me as you let the active workbook save, alter the active workbook via ClearContents and then save ThisWorkbook (workbook with code)?

Private Sub CommandButton1_Click()

On Error GoTo ErrorHandler

Range("A8:F308").ClearContents

With ActiveWorkbook
  .SaveAs Filename:=.Path & "\" & Range("L4") & " " & Range("N4"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End With

ErrorHandler: 'last command in this procedure, procedure will exit automaticly
    
End Sub

Ciao,
Holger

[TBO]

To answer your question, this is a spreadsheet that will be recreated each month and new data will need to be entered and old data erased. So first I save the workbook I am working in to make sure old data in old workbook stays, then I save it as a new workbook for a new month, and then clear the old data from the previous month, then save the workbook again to make sure the old data is gone for good.

I hope that makes sense. Again, I am super new to VBA and so I am not sure if that is the best way to accomplish that, but it made sense in my head and it works great.

Thanks!