Develop Customized Project Management Tools with Excel VBA

The code provided includes several key elements you might need, such as task management, timeline tracking, and report generation.

Objective:

We will create a Project Management tool with the following features:

1. Task Management – To track tasks, their statuses, deadlines, and assigned personnel.
2. Timeline and Gantt Chart – To visually display the project schedule and progress.
3. Reports and Summaries – To generate reports such as task completion status, overdue tasks, etc.
4. Automation – Use VBA to automatically update the tool based on user input.

Features:

• A Task List (to store task names, start dates, end dates, assignees, statuses, etc.)
• A Timeline Gantt Chart (to visualize the timeline)
• Report Generation (to create summaries, overdue tasks, or progress reports)

Step-by-Step Approach

1. Create a Task List in Excel

First, let’s create the structure for storing tasks.

In the first sheet (name it « TaskList »), create the following columns:

• A1: Task ID
• B1: Task Name
• C1: Assignee
• D1: Start Date
• E1: End Date
• F1: Status (Not Started, In Progress, Completed)
• G1: Completion Percentage
• H1: Remarks

In the second sheet (name it « GanttChart »), we will generate the Gantt chart to display the project timeline.

2. VBA Code for Task Management

Let’s write the VBA code to handle the task entry and update logic.

Code to Add a New Task:

Sub AddNewTask()
    Dim taskId As Integer
    Dim taskName As String
    Dim assignee As String
    Dim startDate As Date
    Dim endDate As Date
    Dim status As String
    Dim completion As Integer
    Dim remarks As String    
    ' Get user inputs
    taskId = InputBox("Enter Task ID", "New Task Entry")
    taskName = InputBox("Enter Task Name", "New Task Entry")
    assignee = InputBox("Enter Assignee", "New Task Entry")
    startDate = CDate(InputBox("Enter Start Date (MM/DD/YYYY)", "New Task Entry"))
    endDate = CDate(InputBox("Enter End Date (MM/DD/YYYY)", "New Task Entry"))
    status = InputBox("Enter Status (Not Started, In Progress, Completed)", "New Task Entry")
    completion = InputBox("Enter Completion Percentage (0-100)", "New Task Entry")
    remarks = InputBox("Enter Remarks", "New Task Entry")    
    ' Find the next empty row in the TaskList
    Dim lastRow As Long
    lastRow = Sheets("TaskList").Cells(Sheets("TaskList").Rows.Count, 1).End(xlUp).Row + 1    
    ' Insert data into TaskList sheet
    Sheets("TaskList").Cells(lastRow, 1).Value = taskId
    Sheets("TaskList").Cells(lastRow, 2).Value = taskName
    Sheets("TaskList").Cells(lastRow, 3).Value = assignee
    Sheets("TaskList").Cells(lastRow, 4).Value = startDate
    Sheets("TaskList").Cells(lastRow, 5).Value = endDate
    Sheets("TaskList").Cells(lastRow, 6).Value = status
    Sheets("TaskList").Cells(lastRow, 7).Value = completion
    Sheets("TaskList").Cells(lastRow, 8).Value = remarks
End Sub

Explanation:

• The AddNewTask subroutine uses the InputBox function to prompt the user for task details such as Task ID, Name, Assignee, Start and End Dates, Status, Completion, and Remarks.
• It then finds the next available row in the « TaskList » sheet and inserts the data.

3. VBA Code for Updating Task Status

Now let’s create a macro to update the status and completion percentage of a task.

Code to Update Task Status:

Sub UpdateTaskStatus()
    Dim taskId As Integer
    Dim status As String
    Dim completion As Integer
    Dim row As Long   
    ' Get the task ID to update
    taskId = InputBox("Enter Task ID to Update", "Update Task Status")   
    ' Find the task in the TaskList
    row = Application.Match(taskId, Sheets("TaskList").Range("A:A"), 0)   
    If Not IsError(row) Then
        ' Get the new status and completion percentage from the user
        status = InputBox("Enter new Status (Not Started, In Progress, Completed)", "Update Task Status")
        completion = InputBox("Enter new Completion Percentage (0-100)", "Update Task Status")       
        ' Update the task details in TaskList
        Sheets("TaskList").Cells(row, 6).Value = status
        Sheets("TaskList").Cells(row, 7).Value = completion
    Else
        MsgBox "Task ID not found!"
    End If
End Sub

Explanation:

• The UpdateTaskStatus subroutine prompts the user for a Task ID, then searches the « TaskList » sheet for that task.
• It then allows the user to update the status and completion percentage.

4. Generate a Gantt Chart

We will use conditional formatting and simple Excel formulas to create a visual timeline for the project.

Code to Generate Gantt Chart:

Sub GenerateGanttChart()
    Dim startDate As Date
    Dim endDate As Date
    Dim taskName As String
    Dim row As Long
    Dim col As Long
    Dim currentDate As Date   
    ' Set the start date and end date of the project
    Dim projectStartDate As Date
    Dim projectEndDate As Date   
    projectStartDate = Application.Min(Sheets("TaskList").Range("D:D"))
    projectEndDate = Application.Max(Sheets("TaskList").Range("E:E"))   
    ' Create the header for the Gantt Chart sheet
    Sheets("GanttChart").Cells(1, 1).Value = "Task Name"
    For col = 2 To (projectEndDate - projectStartDate + 2)
        Sheets("GanttChart").Cells(1, col).Value = projectStartDate + col - 2
    Next col  
    ' Fill the Gantt chart with task data
    For row = 2 To Sheets("TaskList").Cells(Sheets("TaskList").Rows.Count, 1).End(xlUp).Row
        taskName = Sheets("TaskList").Cells(row, 2).Value
        startDate = Sheets("TaskList").Cells(row, 4).Value
        endDate = Sheets("TaskList").Cells(row, 5).Value       
        Sheets("GanttChart").Cells(row, 1).Value = taskName       
        ' Format the Gantt chart cells
        For col = 2 To (projectEndDate - projectStartDate + 2)
            currentDate = projectStartDate + col - 2
            If currentDate >= startDate And currentDate <= endDate Then
                Sheets("GanttChart").Cells(row, col).Interior.Color = RGB(0, 102, 204)
            End If
        Next col
    Next row
End Sub

Explanation:

• The GenerateGanttChart subroutine creates a Gantt chart on the « GanttChart » sheet.
• It calculates the project’s start and end date and sets up the Gantt chart timeline.
• It uses conditional formatting to highlight the dates when tasks are active.

5. Generate Reports

Finally, we will create a basic report to show tasks that are overdue or completed.

Code to Generate Report:

Sub GenerateProjectReport()
    Dim lastRow As Long
    Dim reportRow As Long
    Dim status As String
    Dim taskName As String
    Dim completion As Integer
    Dim endDate As Date   
    lastRow = Sheets("TaskList").Cells(Sheets("TaskList").Rows.Count, 1).End(xlUp).Row
    reportRow = 2   
    ' Set up the report headers
    Sheets("Report").Cells(1, 1).Value = "Task Name"
    Sheets("Report").Cells(1, 2).Value = "Status"
    Sheets("Report").Cells(1, 3).Value = "Completion (%)"
    Sheets("Report").Cells(1, 4).Value = "Overdue?"
    ' Loop through tasks and generate report
    For row = 2 To lastRow
        taskName = Sheets("TaskList").Cells(row, 2).Value
        status = Sheets("TaskList").Cells(row, 6).Value
        completion = Sheets("TaskList").Cells(row, 7).Value
        endDate = Sheets("TaskList").Cells(row, 5).Value       
        Sheets("Report").Cells(reportRow, 1).Value = taskName
        Sheets("Report").Cells(reportRow, 2).Value = status
        Sheets("Report").Cells(reportRow, 3).Value = completion       
        If endDate < Date And status <> "Completed" Then
            Sheets("Report").Cells(reportRow, 4).Value = "Yes"
        Else
            Sheets("Report").Cells(reportRow, 4).Value = "No"
        End If       
        reportRow = reportRow + 1
    Next row
End Sub

Explanation:

• The GenerateProjectReport subroutine creates a simple report of tasks, showing whether they are overdue based on their end date and status.

Conclusion:

This is a simple but powerful customized project management tool built with Excel VBA. It provides:

• Task management (add, update tasks).
• Visual timeline (Gantt chart).
• Reports (overdue tasks, completion percentage).

You can extend this further by adding more complex features like automatic email notifications, task prioritization, resource allocation, etc. Let me know if you need any additional features or further explanation on any part of the code!