EXCEL 97

In VBA I save a template with the name specified by a user
(ActiveWorkbook.SaveAs name). When the user enters a name
already saved, XL responds with a message "A file named "name.xls"
already exists in this location. Do you want to replace it?" with options
Yes, No, Cancel.

Is there a way to intercept the user response of Yes, No, or Cancel or
must I use the Dir function in VBA to first check to see if a file exits
and if so respond to the user before the ActiveWorkbook.SaveAs name
is executed.

Bill Agee

Just surround the command with

Application.DisplayAlerts = False
   ActiveWorkbook.SaveAs name
Application.DisplayAlerts = True

Regards,
Tom Ogilvy

Try

Function GetFileName()
    GetFileName = Application.GetOpenFilename("Excel Workbooks
(*.xls),*.xls,All Files (*.*),*.*")
End Function

Sub Test()
    Dim FileName As String
    FileName = GetFileName
    MsgBox FileName
End Sub

This uses the standard file open dialog within Excel but you control
explicitely what happens before and after the dialog is called.

Alternatively, you could use a call to a common dialog control embedded in
any user form but.. you get the windows standard browser rather than the
excel extended browser, if you intend to distribute your workbook you must
make sure all target platforms have the commdlg32.dll installed as it is not
installed by default.

Wouldn't  GetSaveAsFilename  be more appropriate here than GetOpenFilename.

Regards,
Tom Ogilvy