Before Save

[Daviv]

The following is my code. When I cancel the "Save As", the "Save As" window
pops up again. However it does not do it when I cancel the "Save". I am
trying automatically name the file based on a named range but allow the user
to change it as needed or cancel it if they decide it is not ready to save
it. When the 2nd window pops up, it does not suggest the name based on the
named range but the name of the file when it was opened (It is like the
normal SaveAs).

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim FName As Variant
Dim MsgResult As VbMsgBoxResult
Dim InitFileName As String
Dim InitFileNameTo As String


With ActiveWorkbook.Sheets("Daily")

Application.EnableEvents = False
On Error GoTo duplicate

InitFileName = Range("LFile").Value & Range("LProject").Value &
" " & "DIR" & " " & Format(Range("DDateFrom"), "dd-mmm-yy") & ".xls"
FName = Application.GetSaveAsFilename(InitFileName, "Excel File
(*.xls),*.xls")

If FName <> False Then
ActiveWorkbook.SaveAs FName
Cancel = True
Exit Sub
End If

End With

Application.EnableEvents = True


Exit Sub

duplicate:

Application.EnableEvents = True
Cancel = True

End Sub

 

[Tom Ogilvy]

FName = Application.GetSaveAsFilename(InitFileName, "Excel File
(*.xls),*.xls")
If FName <> False Then
ActiveWorkbook.SaveAs FName
Cancel = True
Exit Sub
End If
End With
Application.EnableEvents = True
Exit Sub

in the above code, when you hit Cancel in response to the GetSaveAsFilename
dialog, you skip over the part of the code which sets cancel to true and
therefore never cancel the event that triggered the BeforeSave event. This
action is then performed:

if that action was a Saveas, then the normal SaveAs dialog will be shown
if that action was a Save, then the file will be saved without a dialog