I'm having problems with display alerts that I'm not sure is fixable. I have
a macro that lets your choose a date from a form and then from that it takes
data from one workbook and copies it in to three other workbooks. It then
saves, closes and emails them to certain distribution lists. The problem is
that the form allows the user to potentially pick a previous month that has
already been saved and emailed. If it does then it comes up with an alert
asking the user if they want to overwrite the workbook that already exists.
But for me I would prefer to not even give the user an option. Is there a
way of writing an If statement that says:

If this alert comes up then end the sub and close the workbooks that were
opened and created without saving Else continue running.

Or just something that will basically also choose No when it ask if the user
wants to over write the existing workbook.

Otherwise I fear one day someone will choose Yes and they will send out the
previous months workbooks again.

I think that this may do what you requi

Sub macro3()
fileSaveName = Application.GetSaveAsFilename
If fileSaveName < False Then
' look to see if filename has already been used
n1 = InStrRev(fileSaveName, "\")
fileShtNm = Right(fileSaveName, Len(fileSaveName) - n1)
filePath = Left(fileSaveName, n1 - 1)
Set fs = Application.FileSearch
With fs
.LookIn = filePath
.Filename = fileShtNm
If .Execute 0 Then
MsgBox "This file has already been saved."
Else
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If
End With
End If
End Sub


"carl" wrote:

I'm having problems with display alerts that I'm not sure is fixable. I have
a macro that lets your choose a date from a form and then from that it takes
data from one workbook and copies it in to three other workbooks. It then
saves, closes and emails them to certain distribution lists. The problem is
that the form allows the user to potentially pick a previous month that has
already been saved and emailed. If it does then it comes up with an alert
asking the user if they want to overwrite the workbook that already exists.
But for me I would prefer to not even give the user an option. Is there a
way of writing an If statement that says:

If this alert comes up then end the sub and close the workbooks that were
opened and created without saving Else continue running.

Or just something that will basically also choose No when it ask if the user
wants to over write the existing workbook.

Otherwise I fear one day someone will choose Yes and they will send out the
previous months workbooks again.