Macro to alert when workbook saved or updated?? Is this possible?

[bsnapool]

Is there anyway I would be able to know when a worksheet has been updated by using a macro?

I have 73 files within a folder which are either updated from Monday - Wednesday (Or sometimes not even updated) by managers.

Is there anyway I could automatically find out whether the files have been updated and maybe the date of when it was last saved?

Hope this is all the info you may need.

Many thanks

Andrew

[JakeyC]

One way would be to place a brief string about who saved the file and
when into a specific cell on a sheet. This code uses the BeforeSave
event of the workbook to achieve this (mind the text wrapping) :

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

ThisWorkbook.Sheets(1).Range("A1").Value = "Last saved at " & Time & "
" & Date & _
" By " & Application.UserName

End Sub

This places the time, date and Excel username into cell A1 of the first
sheet, such as "Last saved at 00:04:30 22/07/2006 By Jakey C"


bsnapool wrote:

> Is there anyway I would be able to know when a worksheet has been
> updated by using a macro?
>
> I have 73 files within a folder which are either updated from Monday -
> Wednesday (Or sometimes not even updated) by managers.
>
> Is there anyway I could automatically find out whether the files have
> been updated and maybe the date of when it was last saved?
>
> Hope this is all the info you may need.
>
> Many thanks
>
> Andrew
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile: http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=563912

[Gary Keramidas]

here's some code i use (with ron debruin's help) to put the last saved name and
timestamp on the report sheet when the vacation calendar has been updated and
saved. then it sends an email to the designated people. maybe you can adapt it
to your needs.

this is on the this workbook code page

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim vType As String
Application.ScreenUpdating = False
Worksheets("Reports").Activate
Range("A1").Select
With Worksheets("Reports")
..Unprotect
End With

Range("K2").Value = "Saved by " & Environ("UserName") & " on " & Format(Date, _
"mm/dd/yy") & " at " & Format(Now(), "[$-409]h:mm AM/PM")

With Worksheets("Reports")
..Protect
End With
' send email


Call Mail_Text_in_Body

GoTo Xit
Xit:
Application.ScreenUpdating = True
End Sub
Sub Mail_Text_in_Body()

Dim msg As String, cell As Range
Dim Recipient As String, Subj As String, HLink As String
Dim Recipientcc As String, Recipientbcc As String
msg = "\\servername\sharename" ' path to file being updated
Recipient = "[email protected]"
Recipientcc = "[email protected]"
Recipientbcc = ""

'You can use a cell value also like this for the recipients
'Recipient = Sheets("mysheet").Range("A1").Value

Subj = "Vacation calendar has been updated by " & _
Application.Proper(Environ("UserName"))
HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" & _
Recipientbcc & "&"
HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & msg

ActiveWorkbook.FollowHyperlink (HLink)
Application.Wait (Now + TimeValue("0:00:03"))
Application.SendKeys "%s"

End Sub



--


Gary


"bsnapool" <[email protected]> wrote in
message news:[email protected]...
>
> Is there anyway I would be able to know when a worksheet has been
> updated by using a macro?
>
> I have 73 files within a folder which are either updated from Monday -
> Wednesday (Or sometimes not even updated) by managers.
>
> Is there anyway I could automatically find out whether the files have
> been updated and maybe the date of when it was last saved?
>
> Hope this is all the info you may need.
>
> Many thanks
>
> Andrew
>
>
> --
> bsnapool
> ------------------------------------------------------------------------
> bsnapool's Profile:
> http://www.excelforum.com/member.php...o&userid=36115
> View this thread: http://www.excelforum.com/showthread...hreadid=563912
>