MASTER PROMPT — Excel VBA Project Tracker Dashboard With Live Progress Tracking, Status Bar, DoEvents Animation & Headings-ON Layout INSTRUCTION TO AI You are an expert Excel VBA developer. Generate a single, complete, self-contained VBA module that builds a fully automated, professional Project Tracker Dashboard in Excel, using the architecture below exactly — modeled on a proven Expenses Dashboard build (live status-bar animation, formula-linked KPI cards, formatted PivotCharts, slicers). Non-negotiable rules: - Output the entire VBA code in one code block. No partial code, no placeholders, no "add your logic here" stubs. - Reproduce every Sub, Function, constant, and helper exactly as specified below. Do not merge, rename, omit, or reorder any procedure. - Do not change any RGB colour value, formula string, range address, chart type enum, slicer style, zoom level, row height, column width, or naming convention. - Must run without errors in Excel for Windows (Office 2016+) when the user runs BuildProjectTrackerDashboard. - Application.ScreenUpdating must remain True throughout (live visible progress bar so the build is watchable on screen/video). - The status bar (Application.StatusBar) must show step-by-step progress messages at every sub-step, using a ShowProgress helper with DoEvents + a short Wait pause. WORKBOOK PREREQUISITES Tell the user this before showing the code: The workbook must have a sheet named "Data" with these exact column headers in Row 1 (order doesn't matter, all must be present): - Task ID - Start Date - Due Date - Project - Owner - Status - Priority - % Complete (Extra columns such as "Task Name" are allowed and should simply be ignored by the required-header check.) MODULE-LEVEL CONSTANTS AND VARIABLES Option Explicit Private Const DATA_SHEET As String = "Data" Private Const PIVOT_SHEET As String = "Pivot" Private Const DASH_SHEET As String = "Dashboard" Private Const TABLE_NAME As String = "tblProjectTasks" Private Const PCT_FORMAT As String = "0%" Private BuildStep As String ARCHITECTURE — FULL CALL HIERARCHY (reuse this shape exactly) BuildProjectTrackerDashboard() ← PUBLIC entry point ShowProgress (called throughout) PrepareDataSheet - Validate required headers (RequireHeader for each of the 8 columns above) - Create/refresh Excel Table "tblProjectTasks" over the full data range - Format Start Date / Due Date columns as "dd-mmm-yyyy" - Format "% Complete" as PCT_FORMAT - Style header row bold, white text on dark slate-blue fill RGB(30,58,95) - AutoFit columns BuildPivotSheet - Delete old Dashboard sheet, then old Pivot sheet; add new Pivot sheet after Data - Create PivotCache from tblProjectTasks - Build 7 PivotTables in order, each with its own ShowProgress step: 1. PT_TasksByStatus — Status (rows), Count of Task ID 2. PT_TasksByOwner — Owner (rows), Count of Task ID 3. PT_CompletionTrend — Start Date grouped by Month (rows), Average of % Complete 4. PT_PriorityBreakdown — Priority (rows), Count of Task ID 5. PT_ProjectLoad — Project (rows), Count of Task ID 6. PT_KPI — no row field; data fields: Count of Task ID, Average of % Complete, Count of Task ID filtered later via helper formulas 7. PT_OverdueStatus — a calculated helper column "Is Overdue" (Due Date < Today AND Status <> "Completed") as row field, Count of Task ID as data field - Build chart-helper two-column ranges for each PivotTable (same pattern as BuildTwoColumnHelper in the reference architecture) under Pivot!A50 onward - AutoFit columns BuildDashboardSheet - Delete old Dashboard sheet, add new one Before sheet index 1, name it, Activate - Set ActiveWindow.Zoom = 70 and ActiveWindow.DisplayHeadings = True immediately after Activate, before any drawing (and again as the final authoritative state at the very end of this Sub — do not set DisplayHeadings anywhere else) ApplyDashboardCanvas - Background fill RGB(245, 247, 250), font "Aptos" size 10 - Column widths 7.3 for columns 1–30 - Row heights: rows 1–3 = 22, rows 4–29 = 18 - Gridlines OFF (DisplayHeadings NOT touched here) CreateHeader - Gradient banner across A1:AD3, horizontal, ForeColor RGB(30,58,95) → BackColor RGB(40,160,150) - 42×42 oval icon, glyph "P", font size 22, same relative positioning formula as the reference architecture (bannerLeft+12, vertically centered) - Title textbox: "PROJECT TRACKER DASHBOARD | FY 2025", white, bold, size 44, WordWrap=False, AutoSize=None - Date textbox (width 190, right-aligned in banner): "Data as of:" + today's date, white, bold, size 18, WordWrap=True CreateKPIHelperFormulas - Hidden helper columns AF:AG on Dashboard, GETPIVOTDATA formulas referencing Pivot!$M$3 (PT_KPI) and Pivot!$P$3 (PT_OverdueStatus): AF2 = Total Tasks (Count of Task ID from PT_KPI) AF3 = Completed % (COUNTIFS on tblProjectTasks Status="Completed" / AF2) AF4 = Overdue Count (GETPIVOTDATA from PT_OverdueStatus where Is Overdue=Yes) AF5 = In-Progress Count (COUNTIFS Status="In Progress") AF6 = Average Completion Days (AVERAGEIFS or DATEDIF-based helper column on completed tasks: Due Date - Start Date) AF7 = High-Priority Open Count (COUNTIFS Priority="High" OR "Critical" AND Status<>"Completed") - Number formats: AF2/AF4/AF5/AF7 as "#,##0", AF3 as "0.0%", AF6 as "0.0" CreateKPICards (6 cards, each its own ShowProgress + DoEvents, same AddKPICard helper signature as the reference architecture: address, title, formula, icon glyph, subtitle, accent RGB) 1. A5:E8 "Total Tasks" =Dashboard!$AF$2 "#" "All Tasks" RGB(30,58,95) 2. F5:J8 "Completed %" =Dashboard!$AF$3 "%" "Tasks Done" RGB(40,120,140) 3. K5:O8 "Overdue Tasks" =Dashboard!$AF$4 "!" "Past Due Date" RGB(190,70,50) 4. P5:T8 "In Progress" =Dashboard!$AF$5 ">" "Active Now" RGB(40,160,150) 5. U5:Y8 "High-Priority Open" =Dashboard!$AF$7 "^" "Needs Attention" RGB(210,140,30) 6. Z5:AD8 "Avg Completion (days)" =Dashboard!$AF$6 "T" "Per Task" RGB(60,90,130) CreateDashboardCharts (5 charts, each its own ShowProgress + DoEvents, same AddNormalChart / AddNormalChartFixedCm / FormatChart pattern as the reference architecture, including gradient fills, data labels, and legend rules) 1. "Tasks by Status" xlDoughnut Pivot!A50 → A10:L18 chartKind="status" 2. "Tasks by Owner" xlBarClustered Pivot!D50 → M10:V18 chartKind="owner" 3. "Completion Trend" xlLineMarkers Pivot!G50 → A19:L28 chartKind="trend" 4. "Priority Breakdown" xlColumnClustered Pivot!J50 → M19:V28 chartKind="priority" 5. "Project Load" xlColumnClustered Pivot!M50 → W19:AD28 chartKind="project", using AddNormalChartFixedCm with 7.18 cm height then ×0.8986 shrink (same as the Payment-Method chart in the reference architecture) Legend rules: "owner" chartKind → xlLegendPositionRight; "project" chartKind → HasLegend = False; all others → xlLegendPositionBottom. Doughnut ("status") legend uses fixed pixel values Left=229.614, Width=206.285 (Top/Height still by ratio), same as the reference Category doughnut. CreateProjectSlicer - Position W10:AD14, field "Project", 6 columns, caption "Project", style "SlicerStyleLight6", connected to all 7 PivotTables CreateOwnerSlicer - Position W15:AD18, field "Owner", 6 columns, caption "Owner", style "SlicerStyleLight6", connected to all 7 PivotTables FinalDashboardFormatting - Font "Aptos" across A1:AD29, vertical align center - Hide columns AF:AG - Gridlines off (DisplayHeadings NOT set here) - PageSetup: FitToPagesWide=1, FitToPagesTall=1, Zoom=False - Re-activate Dashboard, re-set Zoom=70, re-set DisplayHeadings=True as final step Shared helpers (always include all, unchanged in behavior from the reference architecture): ShowProgress, Wait, AddPivotToSlicer, PivotDataRowsCount, FormatPivotValues, SafeNumberFormat, DeleteSheetIfExists, LastUsedRow, LastUsedCol, HeaderColumn, RequireHeader, AddKPICard, AddNormalChart, AddNormalChartFixedCm, FormatChart, BuildTwoColumnHelper (rename BuildBudgetHelper to BuildOverdueHelper if a 3-column helper is needed for the overdue chart). CRITICAL BEHAVIOUR FLAGS (Public entry point) On BuildProjectTrackerDashboard: - Application.ScreenUpdating = True (stays True throughout) - Application.DisplayAlerts = False - Application.EnableEvents = False - Application.Calculation = xlCalculationAutomatic - On success: clear status bar, MsgBox "Dashboard Created Successfully" vbInformation - On failure: clear status bar, MsgBox showing BuildStep + Err.Number + Err.Description, vbCritical COMPLETE DESIGN REFERENCE Property Value Font (all) Aptos, 10pt Canvas background RGB(245, 247, 250) Header gradient RGB(30,58,95) → RGB(40,160,150), horizontal Title text "PROJECT TRACKER DASHBOARD | FY 2025", white, bold, size 44 Date text "Data as of:" + date, white, bold, size 18, WordWrap=True Column width (1–30) 7.3 Row height rows 1–3 22 Row height rows 4–29 18 Zoom 70% Gridlines Hidden Headings Visible (True) — set twice: top and end of BuildDashboardSheet KPI card background White, border RGB(208,220,230), weight 0.75, shadow on KPI icon oval 38×38, DiagonalUp gradient, accent → RGB(20,40,70), size 13 KPI value font Bold, size 28, RGB(20,40,70), formula-linked KPI title font Bold, size 18, RGB(45,55,65) KPI subtitle font Size 8, RGB(90,95,100) Chart border RGB(208,220,230), weight 0.7 Chart title colour RGB(20,40,70), bold, size 12 Data label font Size 8, bold, RGB(55,55,55) Number format (percent) 0% Number format (count) #,##0 Number format (days) 0.0 Slicer style SlicerStyleLight6 Slicer fill RGB(247,250,252) Slicer border RGB(200,215,225) Project slicer columns 6 Owner slicer columns 6 Hidden columns AF:AG Page setup FitToPagesWide=1, FitToPagesTall=1, Zoom=False ScreenUpdating True throughout (live animation visible) Completion message MsgBox "Dashboard Created Successfully", vbInformation, "Done" HOW TO USE 1. Open your Excel workbook and ensure the "Data" sheet exists with the required headers (extra columns like "Task Name" are fine). 2. Press Alt+F11 to open the VBA editor. 3. Insert a new standard module (Insert > Module). 4. Paste the complete generated code into the module. 5. Close the VBA editor. 6. Press Alt+F8, select BuildProjectTrackerDashboard, click Run. 7. Watch the status bar for live progress. A success message confirms completion. Return the complete VBA code directly in chat. Do NOT provide a .bas file.