Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Purpose: Check before saving the file if the "as of" date is up to date Dim ws As Worksheet 'Check cell A2 on worksheet "FrontPage_RevTeam" Set ws = Sheets("FrontPage_RevTeam") Dim DateCheck, CheckBeforeSave 'Check that cell A2 has a value in it, then check if that value is a date DateCheck = ws.Range("A2").Value If Not IsEmpty(DateCheck) Then If IsDate(DateCheck) Then 'Check if the workbook has any changes made to it If ActiveWorkbook.Saved = False Then 'Check if the value in A2 is older than today If DateDiff("d", DateCheck, Date) > 1 Then 'Trigger a message box to the user to ask if they wanted to update the date CheckBeforeSave = MsgBox("Did you want to update the date?", vbYesNo + vbQuestion, "DateCheck") 'If yes, then set the cursor into FrontPageRev_Team!A2 If CheckBeforeSave = vbYes Then ws.Range("A2").Activate Else 'If no, trigger the File Save As dialog box Dim bFileSaveAs As Boolean bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical End If End If End If End If End If End Sub
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.Get Access
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Connect with Certified Experts to gain insight and support on specific technology challenges including: