How do I create a pop-up message when a date is greater than or equal to another date?

user_15664967 0
Viewed 92

I have a form that has the current date (C3) and the date that the form was last updated (C7). I need a pop-up message to appear when someone tries to edit the form and it is outdated. Outdated being 2 weeks after the last update (C3 >= C7 + 14).

I want the pop-up to say something along the lines of

"This form is outdated. Please use the most updated version on SharePoint.".

For example, a co-worker is trying to edit the form today (04/16/2021) but he/she is using an old version of the form from 03/01/2021. The information is outdated and I want them to use a newer version that I update to our team SharePoint weekly.

I tried using VBA and Conditional Formatting, but can't find a way for this to work. Any suggestions? Thank you!

if-statement popup validation vba date
2 Answers

You can use the Worksheet_Change() event in VBA - just keep in mind it won't revert the changes already made, but will at least prompt the user to use the new form. If this is acceptable to you, then it's implementation is pretty simple.

Firstly, this code must be placed in the worksheet's code module (not a normal module):

enter image description here

Then just paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.Range("C7").Value < Date - 14 Then
        MsgBox "This form is outdated. Please use the most updated " & _
                        "version on SharePoint.", vbCritical + vbOKOnly
    End If

End Sub

The If statement first checks the value of C7 when a cell update is made. If it passes the test (before 14 days of today's date), then it will prompt the user with a message box.

Keep in mind that this event doesn't actually fire until after the cell is updated. This means that it's impossible to recover the updated cell's data using this event handler alone. This also means that if you wanted to update the date in C7, the event won't fire until the new data is entered. So if the date was 10 years ago but you were to update the date to today, then you will not get the message box since the event fired after the update.

user_57817450

If your intention is that the co-worker shouldn't even use the outdated form, perhaps you can do the check right at the point they open the file?

Place this in the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    With Sheet1 'Change the sheet reference to the correct one
        If .Range("C7").Value < (Date - 14) Then
            MsgBox "This form is outdated. Please use the most updated version on SharePoint.", vbCritical + vbOKOnly
        End If
    End With
End Sub

The code will be triggered by the opening of the workbook immediately.

user_155979360
Related