How To Get A Pop-Up Window Alert In Excel When A Date Is Reached? -

Many people use Excel spreadsheets as a to-do list to help manage their day-to-day activities. However, to ensure that no deadlines are missed It is common for people to want to have an alert system in place. this article will show you how to create an automatic pop-up when a date is reached using VBA code.

Don’t worry if you’re unfamiliar with VBA code instructions in the article below are clear and simple and will allow you to modify the code as required to meet your specific needs quickly and easily.

What The Code Does

The code included within this article is designed to create pop-ups that remind you when a date is due or overdue. Programming is triggered by the opening of the Excel file and will give you reminders of any tasks that are due on the day the file is opened and also any tasks that have become overdue since the last time the file was opened. This will ensure that you don’t receive repeated reminders about the same task.

Setting Up Your File To Create Pop-ups

The set of instructions that we are providing here is designed to set up a file without having to do any modification of the code in this article. However, later on, in the article, we will show you how to modify the code to suit your particular spreadsheet. Alternatively, you can watch the video below on our YouTube channel which will show you how to set up the file and modify the code.

Step 1: Create spreadsheets called “Log” and “To Do List”.

Step 2: Open the VBA editor by hitting ALT + F8. A window will appear with all the macros that are currently in the file, an example of this is shown below. If the existing macros you can select any one of the named macros and click the step into button or if there are no macros present you can click create which will also take you to the VBA editor.

Step 3: Once inside the VBA editor click on the thisworkbook section of the editor which will normally be on the left-hand side of the VBA editor which is shown below.

If this section of the Visual Basic editor does not appear which can happen you can make it appear by selecting view and project Explorer or alternatively hitting the shortcut key CTRL + R.

Step 4: Copy the code below and pasted it into the Thisworkbook section of the VBA editor. An example of what this should look like is provided in the previous step.

'This event code starts when the workbook is opened.
Private Sub Workbook_Open()

Dim LogSheetname As String

LogSheetname = "Log"

'Dimension variable and declare data type
Dim Lrow As Single
 
'Save the row of the first empty cell in column A to variable Lrow.
Lrow = Worksheets(LogSheetname).Range("A" & Rows.Count).End(xlUp).Row + 1
 
'Save text value "Open Workbook" to the first empty cell
Worksheets(LogSheetname).Range("A" & Lrow).Value = "Open workbook"
 
'Save date and time to the corresponding cell in column B.
Worksheets(LogSheetname).Range("B" & Lrow).Value = Date

Call Reminder

End Sub

Step 5: Create a new module in the VBA editor and paste the code below into it. To create a new module select insert and then module. The new module will appear on the left-hand side of the screen. The newly created module and then paste the code into the large white blank area on the right-hand side of the screen.

Sub Reminder()

Dim Sheetname As String
Dim Lastrow As String
Dim LastrowLog As String
Dim Row As Integer
Dim LogSheetname As String

Sheetname = "To Do List"
LogSheetname = "Log"

Lastrow = Worksheets(Sheetname).Range("A" & Rows.Count).End(xlUp).Row
LastrowLog = Worksheets(LogSheetname).Range("A" & Rows.Count).End(xlUp).Row

For Row = 2 To Lastrow Step 1
    
    Select Case Date - Worksheets(Sheetname).Range("B" & Row)

        Case 0 'due today
    
            MsgBox "Task Due(Today): " & Worksheets(Sheetname).Range("A" & Row)
        
        Case Is > 0 'due since file last opened
    
            MsgBox "Task OverDue (" & Date - Worksheets(Sheetname).Range("B" & Row) & " Days): " & Worksheets(Sheetname).Range("A" & Row)

    End Select

Next Row

End Sub

Modifying The Code To Suit Your Spreadsheet

The code at present is specifically designed to use two spreadsheets, Log and To do List and within the to-do list, the code is designed to look down column B to compare due dates and to display the task name which is located in column A.

However, it may be necessary to modify the positions and names of the spreadsheets which is fairly easy to do. To modify the names of the sheets you need to go to both sets of code and change the value of the variables that have been set.

For example, if you wanted to change the name of the sheet to tasks instead of To Do List you need to change the line of code Sheetname = “To Do List” to Sheetname = “Tasks”.

Sub Reminder()

Dim Sheetname As String
Dim Lastrow As String
Dim LastrowLog As String
Dim Row As Integer
Dim LogSheetname As String

Sheetname = "To Do List"  
LogSheetname = "Log"

To change the position of the columns that are being referenced There are a couple of extra places you need to modify the code. These areas are highlighted in bold below. B refers to the column where the due dates are held and A refers to the name of the task.

For Row = 2 To Lastrow Step 1

Select Case Date – Worksheets(Sheetname).Range(“B” & Row)

Case 0 ‘due today

MsgBox “Task Due(Today): ” & Worksheets(Sheetname).Range(“A” & Row)

Case Is > 0 ‘due since file last opened

MsgBox “Task OverDue (” & Date – Worksheets(Sheetname).Range(“B” & Row) & ” Days): ” & Worksheets(Sheetname).Range(“A” & Row)

End Select

Next Row