Suggest a file name OnSave

[Nickis]

I am using Excel 2003.

On selection of Save As I want to suggest the file name based on a value in
a worksheet, and let the user know that. I have the following code, but it's
not working correctly, which I suspect has something to do with the
BeforeSave. It's showing the message, saving the file (with the correct file
name and path), showing the message again and then opening the save directory
window, where you can see the file is already saved.

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

ActiveWorkbook.SaveAs Filename:=Range("A1").Value


Msg = "The workbook should be saved as Project Name and Today's Date -
format ddmmyy"
Style = vbOKOnly
response = MsgBox(Msg, Style, Title, Help, Ctxt)

End Sub

********************

Any suggestions would be appreciated

Thanks Nicki

 

[Paul]

Try

' My_File_Name is a named range in your workbook
' Assuming you wish to save as an Excel file
cFileName =
Application.GetSaveAsFilename(InitialFileName:=Range("My_File_Name").value,
filefilter:="Excel Files (*.xls),*.xls")

 

[Nickis]

Thanks...I think I have to go about this differently.

ps. Sorry...this was a double post...I didn't realize I had already posted