Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to alert when workbook saved or updated?? Is this possible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to alert when workbook saved or updated?? Is this possible?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to alert when workbook saved or updated?? Is this possible?
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 = " Recipientcc = " 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automate email alert when excel workbook get updated | Excel Worksheet Functions | |||
audio alert when a shared workbook is saved by any user | Excel Discussion (Misc queries) | |||
Date last saved/updated | Excel Programming | |||
Run Macro(save workbook) after cell updated | New Users to Excel | |||
How to have:= NOW () date/time change only if updated and saved | Excel Worksheet Functions |