Creating a simple Priority To-Do List with Excel in 13 steps

This is a simple Excel VBA project that illustrates the usage of userforms, conditional formatting, sorting and conditional logic. This will be a fun project to get started with if you are keen on learning Excel VBA. I have shared all my VBA codes and included all my references in the square brackets to make it easier for you!

My To-Do list is automatically sorted by ascending priority score whenever a task is added or deleted.

Priority = Numbers of days left to complete the task (Urgency)+ Importance of the task (with 1 being the most important)

You can create new task and delete completed tasks. The task list automatically sorts itself by priority whenever a new task is added or deleted.

If this is what you want to create, follow this step by step pictorial guide!

A. Adding New Task [1,2]

1. Go to Excel>Developer>View Code> Insert> UserForm

2. Build your input box layout using the elements from the the toolbox window. “Label” is for short text description, “TextBox” is the input box for users and “CommandButton” is used for the submit button.

3. Edit the text in the “Caption” Field on the properties window.

How your input form will look like at the end!

4. Double click on the form. Copy and paste the following VBA code:

Private Sub CommandButton1_Click()
‘Checks if input box is empty. If empty, ask users if they want to proceed’
If TextBox1.Value = “” Or TextBox2.Value = “” Or TextBox3.Value = “” Then
If MsgBox(“Form is not complete. Do you want to continue?”, vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If
End If

‘Creating variables for input values’
Dim deadline As Date
deadline = TextBox2.Value
importance = TextBox3.Value
Days = deadline — Date
Priority = Days + importance

‘Insert input value into selected cell. Active cell would be the cell with the cursor, eg A4. ActiveCell.offset(0,1) would then refer to B4.
ActiveCell = TextBox1.Value
ActiveCell.Offset(0, 1) = Format(TextBox2.Value, “Short Date”)
ActiveCell.Offset(0, 2) = Days
ActiveCell.Offset(0, 3) = importance
ActiveCell.Offset(0, 4) = Priority

‘Shifts cursor to the next row’
ActiveCell.Offset(1, 0).Select

‘Calls resetForm to clear input content in the form’
Call resetForm

‘Calls SortDataWithHeader to sort tasks by priority score’
Call SortDataWithHeader

End Sub

‘Sort table by priority in ascending order’
Sub SortDataWithHeader()
Range(“A4:E23”).Sort Key1:=Range(“E1”), Order1:=xlAscending
End Sub

‘Clear input content in the form’
Sub resetForm()
TextBox1.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
UserForm1.TextBox1.SetFocus

End Sub

5. Go to Sheet 1, copy and paste the following code:

Sub addTask()
UserForm1.Show
UserForm1.TextBox1.SetFocus

End Sub

B. Deleting a Task [2, 3]

1. Insert another UserForm and design the input box to your liking. Double click on the form.

2. Copy and paste the following code:

Private Sub CommandButton1_Click()
‘Checks if input box is empty. If empty, check with user if they want to continue’
If TextBox1.Value = “” Then
If MsgBox(“Form is not complete. Do you want to continue?”, vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If
End If

‘Create variable for row number’
rownum = TextBox1.Value

‘Delete content from the entire row’
Rows(rownum).EntireRow.ClearContents

‘Call SortDataWithHeader to re-sort table by priority in ascending order’
Call SortDataWithHeader

End Sub

Sub SortDataWithHeader()
Range(“A4:E23”).Sort Key1:=Range(“E1”), Order1:=xlAscending
End Sub

3. Copy and paste this in Sheet1.

Sub deleteTask()
UserForm2.Show
UserForm2.TextBox1.SetFocus

End Sub

4. Right click on Shapes>Assign Macro. Select the “Sheet1.addTask” for the “New Task” button. Select the “Sheet1.deleteTask” for the “Delete Task” button.

C. Striking off completed tasks with Checkboxes [4]

1. Insert checkbox.

2. Position the checkbox nicely under the “check” column. Right click on checkbox>Format Control.

Under cell link, type in “$G$4” or any other available cell. This selected cell would display True when box is checked and False when box is unchecked.

3. Go to cell A4 which contains “Task A”. Go to Home> Styles>Conditional Formatting> New Rule> Select “Use a formula…”. Paste the following in:

=IF(G4=TRUE,TRUE,FALSE)

Click on “Format”, check the box for “Strikethrough”.

4. To replicate this format across all the rows under Column A, go to cell A4, select “Format Painter”, select cell A5 to A20 (or to whichever cell you like).

Repeat the steps for creating the checkbox as many times need. Use a different cell for cell link.

5. Save your work in a macro-enabled .xlms file.

YAY WE ARE DONE! If you found this article helpful, I would really appreciate if you could follow my account and give this article a clap! Feel free to comment below and let me know how I can improve on this! Excited to hear about your excel project ideas too! :D

Huge thanks to these amazing references:

[1] https://www.youtube.com/watch?v=1m81T0kFXT0

[2] https://trumpexcel.com/named-ranges-in-excel/#Named-Ranges-in-Excel--An-Introduction

[3] https://www.mrexcel.com/board/threads/clear-contents-of-entire-row.635508/

[4] https://excelchamps.com/blog/strikethrough/