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)
What $ Does In Excel Formulas? How To Use Them And Handy Shortcuts
How To Apply An If Statement If The Cell Only Contains A Partial Match To Text (With And Without VBA)
How To Get Trendline Equation In Excel (2 Methods: In A Cell Or In A Graph)
How Do I Combine Two Macros? (With And Without VBA)