Suggest file name from worksheet and notify user

[Nickis]

I'm using Excel 2003.

On Save I want to populate the "suggested" file name from a value in a
worksheet cell and let the user know what has been done.

Below is what I have, but it's not working like I want. Currently, it saves
the file (and does it correctly), but gives the message twice and then still
takes the user to the File Save Option (where they can see their file has
already been saved).

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

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

Dim ThePath As String
ThePath = ThisWorkbook.Path
ChDir ThePath

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

 

[Patrick Molloy]

yuo shouldn't do a SAveAs within it sown event - thats iterative (ie the
saveas calls itself).
The objective iof the before save to to stes a condition and cancel if its
not met to prevent a save.

The real issue is that you can't test the file name this way.

you need to add your own sub in a standard model that will do the save as
and then you can control the name

 

[Bernie Deitrick]

Nicki,

Try the code below.

HTH,
Bernie
MS Excel MVP

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

Dim ThePath As String
Dim myFName As String
Dim msg As String
Dim Response As VbMsgBoxResult


ThePath = ThisWorkbook.Path
ChDir ThePath

myFName = Application.GetSaveAsFilename(InitialFileName:= _
Range("A1").Value, Title:="Choose a filename - Project Name and Date in ddmmyy format")
msg = "Save the file with the name " & myFName & "?"
Response = MsgBox(msg, vbYesNoCancel)
If Response = vbYes Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs myFName
Application.EnableEvents = True
End If

End Sub

 

[Nickis]

I suspected as much...thanks. I'll play some more to see if I can get to
where I need to be.

 

[Nickis]

Thanks Bernie...but this also has similar problems to my original. I'll try
to work this a different way.

Nicki