Gantt Chart Supercharged - Troubleshooting & Customisation Guide

101ExcelHacks · Starter Edition

Troubleshooting & Customisation Guide

Gantt Chart Supercharged

User Guide and Troubleshooting Manual

By Omowunmi A-Taiwo
www.101excelhacks.co · @101excelhacks

What this guide covers

01

Overview of the ToolWorkbook structure, sheets, and how everything connects

02

Common Reasons Macros Don't RunWhy macros fail and how to unblock them - step by step

03

How to Update or CustomiseEmail notifications, meeting invites, resource directory, periodicity

04

Common Issues & How to Resolve ThemConditional formatting, VBA errors, dropdown problems

05

Best Practice & Safe-Use TipsProtect your workbook from accidental breakage

101ExcelHacks · Starter Tier · Excel Desktop Required
VBA · Outlook Integration · Conditional Formatting

INTRODUCTION

Welcome to the Guide

This guide is your complete reference for setting up, troubleshooting, and customising the Gantt Chart Supercharged - Starter Edition.

The guide is structured to help you quickly diagnose and fix the most common issues, then go further with customisations - changing email content, adjusting meeting schedules, and even switching the chart between daily, weekly, and monthly views.

Before you start: ensure you are using Excel Desktop (Windows or Mac), the file is saved as .xlsm, and macros are enabled. The vast majority of issues trace back to one of these three points.

What's inside

▸

Overview of the ToolWorkbook sheets, how they connect, and what each one does

▸

Common Reasons Macros Don't RunEnable Content, file format, Outlook, IT policy - all covered

▸

How to Update or CustomiseEmails, meeting invites, team directory, Gantt periodicity

▸

Common Issues & How to Resolve ThemBars, alerts, dropdowns, VBA error codes explained

▸

Best Practice & Safe-Use TipsDo's, don'ts, and rules that keep the workbook from breaking

§ 1

Workbook Structure Overview

Sheet Name	Purpose
Gantt	Main project task table and visual Gantt chart bars. This is where you manage tasks, dates, owners, and status.
Team Directory	Resource list: names, emails, and roles. Powers all owner dropdowns and email automation across the workbook.
Config	Project settings: project name, lead, emails, and finalised flag. All macros read their configuration from here.

SECTION 02

Common Reasons Macros Won't Run

Macros not enabled

When you open the file, click Enable Content in the yellow bar. If the bar doesn't appear: File → Options → Trust Center → Trust Center Settings → Macro Settings → select "Enable all macros" or "Enable with notification".

Using Excel on the Web

Excel Online does not support VBA - the code simply will not run. Download the .xlsm file and open it in Excel Desktop (Windows or Mac). All automation features require the desktop application.

Outlook not open or not signed in

The email macros create Outlook mail items via COM automation. If Outlook is closed or no account is signed in, the macro throws an error. Open Outlook and ensure your account is active before running any macro.

File saved as .xlsx

Excel strips all VBA code when saved as .xlsx. Always use File → Save As → Excel Macro-Enabled Workbook (.xlsm). Verify the extension in your file browser before opening.

Macros blocked by Group Policy

Corporate IT may block macros via Windows Group Policy - you cannot override this from within Excel. Contact your IT department and ask them to add the file to Trusted Locations or enable macros for your profile.

Scripting.Dictionary unavailable

On rare configurations, CreateObject("Scripting.Dictionary") may be blocked. In the VBA editor (Alt+F11), go to Tools → References and check Microsoft Scripting Runtime.

Excel Trust Center - Macro Settings dialog

Watch a 1-minute video showing how to enable macros in the Trust Center

SECTION 03 - Email Notifications

How to Update or Customise

Q

How do I change the email subject line?

Open the VBA editor (Alt+F11), go to the GanttNotifications module, and find the .Subject line inside With mailItem. Replace the text string with whatever suits your project.

' Default subject line
.Subject = "📋 Task Assignments - " & projectName

' Change to anything you like, e.g.:
.Subject = "[Action Required] Your tasks on " & projectName

Q

How do I set a reply-to address?

By default, replies go to whichever Outlook account sent the email. To route replies to a specific address instead, add one line inside the With mailItem block.

.ReplyRecipients.Add "[email protected]"

Q

Can I add my logo or signature to emails?

Yes - the email body is plain HTML. Find the footer section inside BuildAssignmentEmailHTML and drop in an image tag pointing to any publicly hosted image URL.

' Add inside the footer div:
html = html & "<img src='https://your-url.com/logo.png'"
html = html & " style='height:40px;margin-top:12px;' />"

Q

How do I CC extra people on task emails?

The CC line in FinalizeAndNotify already sends to your project lead. Add more addresses by separating them with semicolons on the same line.

.CC = leadEmail & ";" & "[email protected]"

Q

How do I change which columns appear in the email table?

The task table defaults to: Task ID, Task Name, Phase, Start Date, Due Date. To remove a column, search for its label in BuildAssignmentEmailHTML. There are exactly two occurrences - one in the header row and one in the data loop. Delete both lines.

Sample email output with annotations showing customisable elements

SECTION 03 - Review Meeting Invites

How to Update or Customise

Q

How do I adjust the meeting duration?

The default meeting runs for one hour (10:00–11:00). Edit the .End time in ScheduleReviewMeetings to change the duration for all auto-generated invites.

' In ScheduleReviewMeetings:
.Start = meetingDate & " 10:00:00"
.End   = meetingDate & " 11:00:00" ' 1 hour

' Change End for a 2-hour meeting:
.End   = meetingDate & " 12:00:00"

Q

How do I change how far in advance meetings are scheduled?

By default, review meetings are created 5 working days before the task end date. Change the number in the SubtractWorkingDays call to any value that suits your project rhythm.

meetingDate = SubtractWorkingDays(taskEndDate, 5)

' Adjust to your preference:
meetingDate = SubtractWorkingDays(taskEndDate, 3)  ' 3 days
meetingDate = SubtractWorkingDays(taskEndDate, 10) ' 10 days

Q

How do I change the meeting location field?

The location field defaults to "Microsoft Teams / Conference Room". Find the .Location line in ScheduleReviewMeetings and replace it with whatever your team uses.

' Default:
.Location = "Microsoft Teams / Conference Room"

' Replace with your preferred default:
.Location = "Boardroom 3, Head Office"

Q

Can I add new keywords that trigger a review invite?

Yes. The tool scans task names for specific keywords and auto-schedules a meeting when it finds a match. Add your own by extending the triggers array and incrementing its size declaration.

Dim triggers(6) As String ' Increase size as needed
triggers(0) = "REVIEW"
triggers(1) = "SIGN-OFF"
triggers(2) = "SIGN OFF"
triggers(3) = "APPROVAL"
triggers(4) = "UAT"
triggers(5) = "WALKTHROUGH" ' New keyword
triggers(6) = "DEMO"          ' Another new keyword

Q

How do I update the suggested meeting agenda?

The agenda is built as plain text inside BuildMeetingBody. Find the AGENDA section and replace the default items with your standard agenda format.

' Default agenda:
body = body & " 1. Review deliverable against criteria" & vbCrLf
body = body & " 2. Feedback and revision requests" & vbCrLf
body = body & " 3. Sign-off or next steps" & vbCrLf

' Custom agenda example:
body = body & " 1. Stakeholder presentation (15 mins)" & vbCrLf
body = body & " 2. Q&A and feedback (20 mins)" & vbCrLf

Auto-generated Outlook meeting invite showing the review meeting title, 10:00 to 11:00 time slot, Microsoft Teams / Conference Room location and pre-filled project, task reference and agenda body

SECTION 03 - Resource Directory

How to Update or Customise

Q

How do I add a new team member?

Go to the Team Directory sheet and add a new row at the bottom with the person's Full Name, Email, Role, and Department. The Owner dropdown in the Gantt sheet will automatically include them the next time it is opened - no additional steps required.

✓

Always add new team members at the bottom of the list, never mid-table. This ensures the named range and row references remain intact.

Q

How do I remove or replace a team member?

Delete or overwrite the row in Team Directory. Note: existing Gantt rows that had that person as Owner will retain their name text but will show a data validation warning (red triangle). Update those Gantt rows manually to a valid owner.

After any directory change, run Check for Changes to ensure notifications go to the correct updated email addresses.

Q

Why isn't the Owner dropdown showing all names?

The data validation dropdown in the Owner column references the named range TeamNames. If you added names below the original range, the named range may not have expanded automatically.

Fix

Formulas → Name Manager → select TeamNames → edit the "Refers To" range to include all rows in your directory, e.g.:='Team Directory'!$A$2:$A$50

SECTION 03 - Gantt Chart Periodicity

How to Update or Customise

The Gantt chart uses weekly columns by default - each column represents one week, with the header showing that week's start date. Some projects are better tracked at daily or monthly granularity. Switching periodicity requires two changes: the date increment between column headers, and the column width for readability at the new scale.

Annotated screenshot showing the date increment and column width for different periodicities

Q

How do I switch to daily columns?

Update the header row dates. Find the first date cell in your Gantt header row (cell K3). Set it to your project start date. In the next cell (L3), enter =K3+1. Drag this formula across all timeline columns. Each column now represents one day.
Narrow the column width. Select all timeline columns, right-click → Column Width, and set to approximately 3-5 (Excel default units). Adjust to taste.
Update date formatting. With narrow columns, long formats like "01-Jan-2025" will not fit. Select the header row and apply a custom format of d (day number only) or d/M (day and month) for legibility.

ℹ

Best for: Short, intensive projects (1–4 weeks) where you need to track daily progress and task handoffs precisely.

Q

How do I switch to monthly columns?

Update the header row dates. Set the first header cell (K3) to the first day of your project's starting month (e.g. 01-Apr-2025). In the next cell, use: =DATE(YEAR(K3),MONTH(K3)+1,1). This always returns the first day of the following month. Drag across all timeline columns.
Format the header row. Apply a custom date format of mmm-yy (e.g. Apr-25) or mmmm yyyy (e.g. April 2025) for readability.
Widen the column width. Monthly columns can be wider since fewer are needed. Set column width to approximately 18–25 depending on how much space you want for the bar colour to show clearly.

⚠

Important: Because each column header represents the first of the month, a task starting 15-Apr and ending 10-May will colour both April and May. This is expected.

SECTION 04

Common Issues & How to Resolve Them

Annotated screenshot showing common issues with conditional formatting rules and their fixes

Q1

Gantt bars are not showing up

Cause

The formula references your Start (col D) and End (col E) dates against the header row. If your layout differs, the column letters will be off.

Fix

Go to Home → Conditional Formatting → Manage Rules. Confirm the formula reads: =AND(G$1>=$D5, G$1<=$E5)
Replace G, D, E and 5 with your actual column letters and first data row.

Q2

Bars show on the wrong rows

Cause

The "Applies to" range in the rule doesn't match your actual data area.

Fix

In Manage Rules, click Edit Rule and update the "Applies to" box to your exact Gantt grid range, e.g. $G$5:$CZ$100.

Q3

All rows are turning red

Cause

The overdue formula is referencing the wrong or empty Status column, so the status check always returns FALSE.

Fix

Confirm your Status column letter. If it's column H, the formula should be:
=AND($E5<TODAY(),$H5<>"Complete")

Q4

Completed rows are not turning grey

Cause

The "Complete" text in the Status column doesn't exactly match the CF formula - capitalisation or spacing difference.

Fix

Check your Status dropdown values. If you use "Completed", update the formula to: =$H5="Completed"

SECTION 05

Best Practice & Safe-Use Tips

Following these guidelines will prevent the most common accidental breakages. The workbook is built around a set of structural assumptions - understanding them means you can customise freely without causing errors.

⚠ Do Not

Delete sheets, columns, or rows

The VBA macros reference sheets by their exact tab names and cells by fixed column positions. Deleting any sheet (Gantt, Config, Team Directory) or any structural column will break macro execution immediately.

⚠ Do Not

Insert rows or columns mid-table

Inserting rows or columns in the middle of the Gantt or Team Directory tables shifts cell references and breaks conditional formatting ranges. Always add new data at the bottom of the existing list.

⚠ Do Not

Save as .xlsx

Excel strips all VBA code when you save as a standard .xlsx file. Always use File → Save As → Excel Macro-Enabled Workbook (.xlsm). Check the extension before every save.

⚠ Do Not

Rename the sheet tabs

The macros look for sheets named exactly Gantt, Team Directory, and Config (case-sensitive). Renaming a tab causes Error 9 (Subscript out of range) on every macro that references it.

✓ Do

Add new team members at the bottom

To add a new person to Team Directory, append their row after the last existing entry. The Owner dropdown will pick them up automatically. Never insert them mid-list.

✓ Do

Add new tasks at the bottom of the Gantt

New task rows should be added below the last existing task. Copy the full row format (including data validation) from an adjacent row rather than typing into a blank, unformatted row.

✓ Do

Use the Config sheet as your control panel

Project name, lead email, and stakeholder lists all live in Config. Update them there - you never need to edit the VBA code for these values. Keep Config populated before finalising.

✓ Do

Back up before editing VBA

Use File → Save a Copy with a version number before making any changes inside the VBA editor. A clean backup takes 10 seconds and saves hours of debugging.

📅 Date Rule

Always format date cells as Date, not Text

Date columns in the Gantt sheet must be formatted as Short Date. Text-formatted dates prevent bars from appearing, break overdue alerts, and cause Error 13 in macros. If in doubt, delete and re-enter the value.

📧 Email Testing

Test with your own email first

Before finalising to the full team, set a test task's Owner to your own name and email in Team Directory. Click Finalise and verify the email arrives correctly formatted before sending to everyone.

SECTION 04 - VBA Error Reference

Error Codes & What They Mean

Macro errors are very unlikely to occur, but in the unlikely event they do, Excel shows an error number and description. Use this table to diagnose and fix the issue quickly.

Error	Likely Cause	Fix
429 ActiveX can't create object	Outlook is not installed or not running	Open Outlook and sign in, then re-run the macro
91 Object variable not set	A sheet reference (Gantt, Config, Team Directory) can't be found - usually a sheet name mismatch	Check all sheet tabs are named exactly: `Gantt`, `Team Directory`, `Config` (case-sensitive)
9 Subscript out of range	The macro is looking for a sheet or array index that doesn't exist	Verify all required sheet names exist. Check no sheets have been renamed or deleted
13 Type mismatch	A date cell contains text instead of a real date value, or a number cell contains text	Reformat date columns as Date (not Text). Delete and re-enter any problem cells
1004 Application-defined error	The macro is trying to write to a locked or invalid cell	Check the target sheet is not protected. Unprotect if needed: Review → Unprotect Sheet
70 Permission denied	Outlook is open but blocking programmatic access (security prompt not accepted)	In Outlook: File → Options → Trust Center → Programmatic Access → set to "Never warn me about suspicious activity"
438 Object doesn't support this property	VBA is referencing an Outlook object property that doesn't exist in your Outlook version	Ensure Outlook is fully updated. Late binding is already used by default in this workbook

💡

Quick diagnosis tip: The three most common errors in this workbook are 429 (Outlook not open), 91 (wrong sheet name), and 13 (text in date column). Check these three first before investigating further.