Display message after selecting NO/Cancel when saving file

[EssKayKay]

Is there a way to display a message box if I select NO or CANCEL to the following popup warning question when saving a file?
A file named C:\Users\xxxxx\xxxx.xls already exists in this location. Do you want to replace it? <YES> <NO> <CANCEL>
I don’t mind seeing the message but I’d like to display another message after No/Cancel.

Thanks for viewing,
Steve K.

 

[Jaafar Tribak]

There is no built-in way that I know of to achieve what you want.
However, this specific situation is probably a good candidate for the use of the win32 api albeit it is not going to be easy to code.
The SaveAsUI argument of the Workbook_BeforeSave event can come in handy to detect the display of the Save As and Confirm Save As Dialog Boxes which can then be subclassed to detect when the No/Cancel are activated.
Is this for a single workbook or application-wide ?

 

[EssKayKay]

There is no built-in way that I know of to achieve what you want.
However, this specific situation is probably a good candidate for the use of the win32 api albeit it is not going to be easy to code.
The SaveAsUI argument of the Workbook_BeforeSave event can come in handy to detect the display of the Save As and Confirm Save As Dialog Boxes which can then be subclassed to detect when the No/Cancel are activated.
Is this for a single workbook or application-wide ?

Thank you Jaafar for your quick response. With no easy way to achieve this I'll pass on my inquiry. I will find some other way to check if the file exists before saving then display message(s) accordingly.

Again, my thanks. . .
Steve

 

[Jaafar Tribak]

Are you saving the workbook manually via the User Interface ( File | SaveAs Menu ) or via code ? I am asking because the dialog box with the Yes\No\Cancel is different in each case and the code will also be different.

Is this the prompt dialog box we are talking about?

 

[EssKayKay]

Are you saving the workbook manually via the User Interface ( File | SaveAs Menu ) or via code ? I am asking because the dialog box with the Yes\No\Cancel is different in each case and the code will also be different.

Is this the prompt dialog box we are talking about?
View attachment 120741

Yup, that's the message. I believe I have a solution where I check if the file exists. If so, delete the existing backup and re-backup. Here's my routine:

Sub CheckFileExists()

'------ Set variables ------
Dim FilePath, FileOnly, PathOnly, BackupName, aFile As String

FilePath = ThisWorkbook.FullName
FileOnly = ThisWorkbook.Name
PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))

Range("T5") = PathOnly
Range("T6") = FileOnly '--- Current FileName
Range("V6") = FileOnly '--- Original FileName

FilePath = Range("T5") & Range("X6") & "_" & Format(Date, "yyyymmdd") & ".xlsb"
BackupName = Range("X6") & "_" & Format(Date, "yyyymmdd") & ".xlsb"
aFile = ActiveWorkbook.Path & Application.PathSeparator & BackupName

If Dir(aFile) = "" Then
    Call Backup
Else
    Dim DoIt As Integer
    DoIt = MsgBox(" The BackUp file already exists." & vbNewLine & _
         " Do you wish to replace it?", vbYesNo, " File: " & FileOnly)
    If DoIt = vbYes Then
        Kill aFile
       Call Backup
   Else
       MsgBox " Backup aborted. . ."
   End If
End If

End Sub

If there's an easier way, I would most certainly be interested.

Thanks again Jaafar,
Steve K.

 

[Jaafar Tribak]

If there's an easier way, I would most certainly be interested.

Your code is fine since the built-in excel dialog prompt is never displayed and is replaced by the custom MsgBox. No need to do anything else.

 

[EssKayKay]

Your code is fine since the built-in excel dialog prompt is never displayed and is replaced by the custom MsgBox. No need to do anything else.

Thank you Jaafar. . .