Maybe...

But what happens if the user wants to close the workbook without saving and then
your macro saves the file automatically.

If the user were closing without saving because of a major mistake (deleting too
much data/worksheets), then you may have bigger problems.

Instead I'd put a big old button in a prominent location (top row of the
worksheet) and have it run the macro.

Then you could control exactly what you want to happen. You will have to make
sure that the user learns to use your button, though.

You could stop the user from using the built-in save options by putting code
like this behind the ThisWorkbook module:

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

Cancel = True

MsgBox "Please use the big old button to save!" _
& vbLf & vbLf & "SAVE DID NOT WORK!"

End Sub

And then this would be the code that would be assigned to the Button from the
Forms toolbar (in a General module):


Option Explicit
Sub SaveMeNow()

Dim myPath As String
Dim myFileName As String

myPath = "C:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFileName = "DailyLog_" & Format(Now, "yyyymmdd-hhmmss") & ".xls"

Application.EnableEvents = False
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=myPath & myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

All this depends on the user allowing macros to run, too.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


paankadu wrote:

Is there a way to have a macro automatically save a file when closing it
down? Specifically looking for it to save in a certain location using the
date as part of the file name. It is a daily log and would like it to save
as "10.26.09daily_log" when the user updates the information and closes out
of it for the day. I have the form set up as a template currently.

Reason being is many of the users aren't computer savvy and to do this they
have to do file save as and then it gets saved in different folders,
different ways and it is a search mission to find all the locations. they
are comfortable opening the folder & entering the data but we would like them
to just be able to close out the worksheet and have it automatically save to
the current date and file name.

Thanks in advance for your assitance.
--
Thanks,
Angie

--

Dave Peterson