Learn how to build a professional Excel task pane using VBA and embedded HTML, creating a persistent side-panel interface with two-way communication between JavaScript and VBA. Go from raw UserForm to a fully styled control panel with live KPIs, navigation, filters, and action buttons.
Picture this: you've built a sophisticated Excel workbook for your finance team — a multi-sheet model with dynamic dashboards, automated reporting, and custom VBA logic. It's powerful. It's also completely opaque to anyone who didn't build it. Your colleagues keep opening the wrong sheets, clicking the wrong buttons, triggering macros out of order, or asking you the same three questions over Slack every Monday morning. What you need isn't better documentation. You need a proper interface.
Task panes — those side-panel UI elements you see in Word's "Styles" panel or Excel's "Format Cells" sidebar — are the professional answer to this problem. Unlike modal dialog boxes or pop-up forms, task panes sit persistently alongside the spreadsheet. Users can interact with filters, controls, and navigation without losing their place in the data. They look native, they feel intentional, and they communicate that this workbook is a product, not a hack.
In this lesson, you'll build a fully functional custom task pane from scratch using VBA and the WebBrowser ActiveX control, embedding real HTML/CSS/JavaScript inside the panel to create a rich, interactive interface. By the end, you'll have a working side-panel that can read cell data, fire VBA procedures, update workbook state, and present dynamic UI elements — all controlled from a polished sidebar that stays visible while users work.
What you'll learn:
WebBrowser control works and why it's the foundation for custom task panes in classic ExcelYou should be comfortable with:
<div> and a function)Before writing a line of code, let's be clear about what we're building and why this architecture works.
Excel's ribbon-based task pane (like the one exposed by Office Add-ins built with JavaScript) requires a full VSTO or Office.js setup — too heavy for most VBA-centric workflows. What we're doing instead is leveraging the WebBrowser ActiveX control, a genuine Internet Explorer rendering engine embedded inside a UserForm. The UserForm is configured to be non-modal, positioned to the right of the workbook window, and sized to look exactly like a native task pane.
The HTML document inside the WebBrowser control becomes our UI canvas. JavaScript in that document can call back into VBA via the window.external object, which is an exposed interface that VBA satisfies through a technique called ObjectContext. VBA, in turn, can manipulate the HTML document through the WebBrowser.Document property, reading and writing DOM elements directly.
This gives us:
The result is a genuine two-way communication channel between your VBA logic and your HTML interface.
Note: This approach uses the Internet Explorer rendering engine (Trident), which is embedded in Windows via the
WebBrowserActiveX control. This makes it a Windows-only solution — it won't run on Mac Excel. It works reliably on Excel 2010 through Excel 365 on Windows. For cross-platform needs, Office Add-ins with the JavaScript API are the alternative path.
Open the VBA editor (Alt+F11) and insert a new UserForm. Name it TaskPaneForm. Now we'll transform this standard dialog box into something that behaves like a persistent side panel.
In the Properties window (press F4 if it's not visible), set these properties on TaskPaneForm:
Caption: Report Control PanelShowModal: False — this is critical; it allows the user to interact with the spreadsheet while the pane is openWidth: 250Height: 600StartUpPosition: 0 - ManualBorderStyle: 0 - fmBorderStyleNoneFrom the Toolbox (if it's open — use View > Toolbox), you need to add the Microsoft Web Browser control. Right-click the Toolbox and choose "Additional Controls." In the list, find "Microsoft Web Browser" and check the box. It will appear as a globe icon in the Toolbox.
Drag the WebBrowser control onto TaskPaneForm and set these properties:
Name: wbPanelLeft: 0Top: 0Width: 246Height: 596The control should fill the entire form.
Now open the standard module (Insert > Module) and write the procedure that will launch and position the pane:
Public taskPane As TaskPaneForm
Sub ShowTaskPane()
' Don't open a second instance if already open
If Not taskPane Is Nothing Then
If taskPane.Visible Then
taskPane.SetFocus
Exit Sub
End If
End If
Set taskPane = New TaskPaneForm
' Position the form to the right of the Excel window
Dim excelLeft As Long
Dim excelTop As Long
Dim excelWidth As Long
Dim paneWidth As Long
paneWidth = 250
excelLeft = Application.Left
excelTop = Application.Top
excelWidth = Application.Width
taskPane.Left = excelLeft + excelWidth - paneWidth - 10
taskPane.Top = excelTop + 80
taskPane.Height = Application.Height - 120
taskPane.Show vbModeless
End Sub
Sub HideTaskPane()
If Not taskPane Is Nothing Then
Unload taskPane
Set taskPane = Nothing
End If
End Sub
The key line is taskPane.Show vbModeless — this keeps the task pane open while the user works in the spreadsheet. Without vbModeless, the form blocks all interaction.
Tip: Assign
ShowTaskPaneto a button in your ribbon or a keyboard shortcut so users can toggle the pane easily. You can also call it from theWorkbook_Openevent so the pane launches automatically when the file opens.
Here's where the fun begins. We're going to define the entire HTML interface as a VBA string constant and load it into the WebBrowser control when the form initializes.
In the TaskPaneForm code module (double-click the form in the Project Explorer), add the following. We'll start with the HTML generation function:
Private Sub UserForm_Initialize()
LoadPanelHTML
End Sub
Private Sub LoadPanelHTML()
Dim html As String
html = BuildPanelHTML()
' Write to temp file and navigate to it
Dim tempPath As String
tempPath = Environ("TEMP") & "\ExcelTaskPane.html"
Dim fileNum As Integer
fileNum = FreeFile
Open tempPath For Output As #fileNum
Print #fileNum, html
Close #fileNum
wbPanel.Navigate tempPath
End Sub
We write the HTML to a temp file rather than using document.write, because navigating to a local file gives us a full document context with reliable CSS and JavaScript support.
Now, let's build the actual HTML. This is the core of our interface — a professional control panel for a financial reporting workbook:
Private Function BuildPanelHTML() As String
Dim h As String
h = "<!DOCTYPE html><html><head><meta charset='UTF-8'>"
h = h & "<style>"
h = h & "* { box-sizing: border-box; margin: 0; padding: 0; font-family: Segoe UI, Arial, sans-serif; }"
h = h & "body { background: #f3f4f6; color: #1f2937; font-size: 13px; }"
h = h & ".header { background: #1e3a5f; color: white; padding: 14px 12px; }"
h = h & ".header h2 { font-size: 14px; font-weight: 600; }"
h = h & ".header p { font-size: 11px; opacity: 0.8; margin-top: 3px; }"
h = h & ".section { padding: 12px; border-bottom: 1px solid #e5e7eb; }"
h = h & ".section-title { font-size: 10px; font-weight: 700; text-transform: uppercase; "
h = h & " letter-spacing: 0.8px; color: #6b7280; margin-bottom: 8px; }"
h = h & "select, input { width: 100%; padding: 6px 8px; border: 1px solid #d1d5db; "
h = h & " border-radius: 4px; font-size: 12px; color: #374151; background: white; margin-bottom: 8px; }"
h = h & "select:focus, input:focus { outline: none; border-color: #1e3a5f; }"
h = h & ".btn { display: block; width: 100%; padding: 8px 12px; border: none; "
h = h & " border-radius: 4px; font-size: 12px; font-weight: 600; cursor: pointer; "
h = h & " margin-bottom: 6px; text-align: center; }"
h = h & ".btn-primary { background: #1e3a5f; color: white; }"
h = h & ".btn-primary:hover { background: #2d4f7c; }"
h = h & ".btn-secondary { background: #e5e7eb; color: #374151; }"
h = h & ".btn-secondary:hover { background: #d1d5db; }"
h = h & ".btn-danger { background: #dc2626; color: white; }"
h = h & ".btn-danger:hover { background: #b91c1c; }"
h = h & ".status-bar { padding: 8px 12px; font-size: 11px; }"
h = h & ".status-ok { background: #d1fae5; color: #065f46; border-left: 3px solid #10b981; }"
h = h & ".status-warn { background: #fef3c7; color: #92400e; border-left: 3px solid #f59e0b; }"
h = h & ".status-err { background: #fee2e2; color: #991b1b; border-left: 3px solid #dc2626; }"
h = h & ".kpi-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 6px; }"
h = h & ".kpi-card { background: white; border: 1px solid #e5e7eb; border-radius: 4px; padding: 8px; }"
h = h & ".kpi-label { font-size: 10px; color: #6b7280; margin-bottom: 2px; }"
h = h & ".kpi-value { font-size: 15px; font-weight: 700; color: #1e3a5f; }"
h = h & ".nav-btn { background: white; border: 1px solid #e5e7eb; text-align: left; "
h = h & " padding: 8px 10px; margin-bottom: 4px; border-radius: 4px; font-size: 12px; "
h = h & " cursor: pointer; display: block; width: 100%; }"
h = h & ".nav-btn:hover { background: #eff6ff; border-color: #bfdbfe; }"
h = h & ".nav-icon { margin-right: 6px; }"
h = h & "</style></head><body>"
' Header
h = h & "<div class='header'>"
h = h & " <h2>📊 Report Control Panel</h2>"
h = h & " <p>Financial Reporting Suite v2.1</p>"
h = h & "</div>"
' Status bar
h = h & "<div id='statusBar' class='status-bar status-ok'>✓ All systems ready</div>"
' KPI section
h = h & "<div class='section'>"
h = h & " <div class='section-title'>Current Period KPIs</div>"
h = h & " <div class='kpi-grid'>"
h = h & " <div class='kpi-card'><div class='kpi-label'>Revenue</div>"
h = h & " <div class='kpi-value' id='kpiRevenue'>--</div></div>"
h = h & " <div class='kpi-card'><div class='kpi-label'>Margin %</div>"
h = h & " <div class='kpi-value' id='kpiMargin'>--</div></div>"
h = h & " <div class='kpi-card'><div class='kpi-label'>Variances</div>"
h = h & " <div class='kpi-value' id='kpiVariances'>--</div></div>"
h = h & " <div class='kpi-card'><div class='kpi-label'>Last Updated</div>"
h = h & " <div class='kpi-value' id='kpiUpdated'>--</div></div>"
h = h & " </div>"
h = h & "</div>"
' Navigation section
h = h & "<div class='section'>"
h = h & " <div class='section-title'>Navigate</div>"
h = h & " <button class='nav-btn' onclick='vbaCall(""GoToSheet"", ""Dashboard"")'>"
h = h & " <span class='nav-icon'>📈</span>Dashboard</button>"
h = h & " <button class='nav-btn' onclick='vbaCall(""GoToSheet"", ""P&L"")'>"
h = h & " <span class='nav-icon'>💲</span>P&L Statement</button>"
h = h & " <button class='nav-btn' onclick='vbaCall(""GoToSheet"", ""Budget"")'>"
h = h & " <span class='nav-icon'>🏗</span>Budget vs Actual</button>"
h = h & " <button class='nav-btn' onclick='vbaCall(""GoToSheet"", ""RawData"")'>"
h = h & " <span class='nav-icon'>📄</span>Raw Data</button>"
h = h & "</div>"
' Filters section
h = h & "<div class='section'>"
h = h & " <div class='section-title'>Report Filters</div>"
h = h & " <select id='selPeriod' onchange='vbaCall(""ApplyPeriodFilter"", this.value)'>"
h = h & " <option value=''>-- Select Period --</option>"
h = h & " <option value='Q1-2025'>Q1 2025</option>"
h = h & " <option value='Q2-2025'>Q2 2025</option>"
h = h & " <option value='Q3-2025'>Q3 2025</option>"
h = h & " <option value='Q4-2025'>Q4 2025</option>"
h = h & " </select>"
h = h & " <select id='selDepartment' onchange='vbaCall(""ApplyDeptFilter"", this.value)'>"
h = h & " <option value='ALL'>All Departments</option>"
h = h & " <option value='SALES'>Sales</option>"
h = h & " <option value='OPS'>Operations</option>"
h = h & " <option value='MKTG'>Marketing</option>"
h = h & " <option value='TECH'>Technology</option>"
h = h & " </select>"
h = h & "</div>"
' Actions section
h = h & "<div class='section'>"
h = h & " <div class='section-title'>Actions</div>"
h = h & " <button class='btn btn-primary' onclick='vbaCall(""RefreshAllData"", """")'>"
h = h & " ↻ Refresh All Data</button>"
h = h & " <button class='btn btn-primary' onclick='vbaCall(""GenerateReport"", """")'>"
h = h & " 📄 Generate PDF Report</button>"
h = h & " <button class='btn btn-secondary' onclick='vbaCall(""ExportToCSV"", """")'>"
h = h & " ⇓ Export to CSV</button>"
h = h & " <button class='btn btn-danger' onclick='vbaCall(""ClearFilters"", """")'>"
h = h & " ✕ Clear All Filters</button>"
h = h & "</div>"
' JavaScript bridge
h = h & "<script>"
h = h & "function vbaCall(proc, arg) {"
h = h & " try {"
h = h & " window.external.RunVBA(proc, arg);"
h = h & " } catch(e) {"
h = h & " setStatus('Error calling ' + proc + ': ' + e.message, 'err');"
h = h & " }"
h = h & "}"
h = h & "function setStatus(msg, level) {"
h = h & " var bar = document.getElementById('statusBar');"
h = h & " bar.className = 'status-bar status-' + level;"
h = h & " var icon = level === 'ok' ? '✓ ' : level === 'warn' ? '⚠ ' : '✕ ';"
h = h & " bar.innerHTML = icon + msg;"
h = h & "}"
h = h & "function updateKPI(id, value) {"
h = h & " var el = document.getElementById(id);"
h = h & " if (el) el.innerHTML = value;"
h = h & "}"
h = h & "</script>"
h = h & "</body></html>"
BuildPanelHTML = h
End Function
This is a complete, styled HTML document. Let's unpack the architecture before moving on:
kpiRevenue, kpiMargin, etc.) have IDs so VBA can push live data into them from the spreadsheetvbaCall(procedureName, argument) — a JavaScript bridge functionvbaCall function invokes window.external.RunVBA(proc, arg) — and we need to implement that bridge on the VBA sideThe window.external object in the WebBrowser control exposes the VBA UserForm object itself — but only if we implement a specific interface. We do this by adding a public method to the TaskPaneForm that the JavaScript can call.
In the TaskPaneForm code module, add this procedure:
Public Sub RunVBA(ByVal ProcName As String, ByVal Arg As String)
' Route JavaScript calls to the correct VBA procedure
Select Case ProcName
Case "GoToSheet"
Call GoToSheet(Arg)
Case "ApplyPeriodFilter"
Call ApplyPeriodFilter(Arg)
Case "ApplyDeptFilter"
Call ApplyDeptFilter(Arg)
Case "RefreshAllData"
Call RefreshAllData
Case "GenerateReport"
Call GenerateReport
Case "ExportToCSV"
Call ExportToCSV
Case "ClearFilters"
Call ClearFilters
Case Else
SetPaneStatus "Unknown command: " & ProcName, "warn"
End Select
End Sub
Warning: The
window.external.RunVBAcall only works because theWebBrowsercontrol exposes the UserForm's public interface to the HTML document. You must declareRunVBAasPublic Subin the form's code module — not in a standard module. If you put it in the wrong place, the JavaScript call will silently fail.
Now implement the procedures. These are real business-logic handlers that tie the UI to your workbook:
Private Sub GoToSheet(SheetName As String)
On Error GoTo SheetNotFound
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(SheetName)
ws.Activate
ws.Range("A1").Select
SetPaneStatus "Navigated to " & SheetName, "ok"
Exit Sub
SheetNotFound:
SetPaneStatus "Sheet '" & SheetName & "' not found", "err"
End Sub
Private Sub ApplyPeriodFilter(Period As String)
If Period = "" Then Exit Sub
On Error GoTo FilterError
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RawData")
' Apply AutoFilter on the Period column (column B)
With ws
If .AutoFilterMode Then .AutoFilterMode = False
.Range("A1").AutoFilter
.Range("A1").AutoFilter Field:=2, Criteria1:=Period
End With
SetPaneStatus "Period filter: " & Period, "ok"
UpdateKPIDisplay
Exit Sub
FilterError:
SetPaneStatus "Filter failed: " & Err.Description, "err"
End Sub
Private Sub ApplyDeptFilter(Department As String)
On Error GoTo FilterError
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RawData")
If Department = "ALL" Then
' Remove department filter only
If ws.AutoFilterMode Then
ws.AutoFilterMode = False
ws.Range("A1").AutoFilter
End If
SetPaneStatus "Showing all departments", "ok"
Else
ws.Range("A1").AutoFilter Field:=3, Criteria1:=Department
SetPaneStatus "Dept filter: " & Department, "ok"
End If
UpdateKPIDisplay
Exit Sub
FilterError:
SetPaneStatus "Filter failed: " & Err.Description, "err"
End Sub
Private Sub RefreshAllData()
SetPaneStatus "Refreshing data...", "warn"
DoEvents ' Let the UI update
On Error GoTo RefreshError
' Refresh all Power Query connections
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
conn.Refresh
Next conn
UpdateKPIDisplay
SetPaneStatus "Data refreshed: " & Format(Now, "h:mm AM/PM"), "ok"
Exit Sub
RefreshError:
SetPaneStatus "Refresh failed: " & Err.Description, "err"
End Sub
Private Sub GenerateReport()
SetPaneStatus "Generating PDF...", "warn"
DoEvents
On Error GoTo ExportError
Dim outputPath As String
outputPath = Environ("USERPROFILE") & "\Desktop\FinancialReport_" & _
Format(Date, "YYYYMMDD") & ".pdf"
ThisWorkbook.Worksheets(Array("Dashboard", "P&L", "Budget")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
' Return to dashboard
ThisWorkbook.Worksheets("Dashboard").Select
SetPaneStatus "PDF saved to Desktop", "ok"
Exit Sub
ExportError:
SetPaneStatus "Export failed: " & Err.Description, "err"
End Sub
Private Sub ExportToCSV()
On Error GoTo ExportError
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RawData")
Dim csvPath As String
csvPath = Environ("USERPROFILE") & "\Desktop\RawData_Export_" & _
Format(Date, "YYYYMMDD") & ".csv"
' Copy to new workbook and save as CSV
ws.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV
ActiveWorkbook.Close False
Application.DisplayAlerts = True
SetPaneStatus "CSV exported to Desktop", "ok"
Exit Sub
ExportError:
SetPaneStatus "CSV export failed: " & Err.Description, "err"
End Sub
Private Sub ClearFilters()
On Error Resume Next
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RawData")
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Range("A1").AutoFilter
On Error GoTo 0
SetPaneStatus "All filters cleared", "ok"
UpdateKPIDisplay
End Sub
This is the magic direction: VBA reading spreadsheet data and pushing it into the HTML interface. Use the WebBrowser.Document object to access the DOM directly:
Public Sub UpdateKPIDisplay()
' Read KPI values from the Dashboard sheet's named cells
On Error GoTo UpdateError
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Dashboard")
Dim revenue As String
Dim margin As String
Dim variances As String
' Read from named ranges (or specific cells)
revenue = FormatCurrency(ws.Range("KPI_Revenue").Value, 1, , , True)
margin = Format(ws.Range("KPI_Margin").Value, "0.0%")
variances = CStr(ws.Range("KPI_VarianceCount").Value)
' Push to HTML via DOM manipulation
Dim doc As Object
Set doc = wbPanel.Document
If doc Is Nothing Then Exit Sub
doc.getElementById("kpiRevenue").innerHTML = revenue
doc.getElementById("kpiMargin").innerHTML = margin
doc.getElementById("kpiVariances").innerHTML = variances
doc.getElementById("kpiUpdated").innerHTML = Format(Now, "h:mm")
Exit Sub
UpdateError:
' Named ranges may not exist in a demo workbook — fail silently
End Sub
Public Sub SetPaneStatus(Message As String, Level As String)
' Level: "ok", "warn", "err"
On Error Exit Sub
Dim doc As Object
Set doc = wbPanel.Document
If doc Is Nothing Then Exit Sub
' Call the JavaScript setStatus function directly
doc.parentWindow.setStatus(Message, Level)
End Sub
Key insight:
wbPanel.Documentgives you the HTML document object, and from there the entire DOM is accessible —getElementById,innerHTML,style,className, all of it. This is a genuine live reference: changinginnerHTMLinstantly updates what's visible in the panel. You don't need to reload the page.
We want the KPI cards to update whenever the user changes something in the spreadsheet. The cleanest way to do this is through worksheet events. In the ThisWorkbook module, or the Dashboard sheet's module, add:
' In the Dashboard sheet's code module:
Private Sub Worksheet_Calculate()
' Refresh panel whenever the sheet recalculates
If Not taskPane Is Nothing Then
If taskPane.Visible Then
taskPane.UpdateKPIDisplay
End If
End If
End Sub
' In ThisWorkbook module:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not taskPane Is Nothing Then
If taskPane.Visible Then
taskPane.UpdateKPIDisplay
End If
End If
End Sub
This pairs naturally with Building a Custom VBA Event-Driven Framework: Respond to Workbook, Worksheet, and Application Events for Real-Time Automation — worksheet events are the right hook for keeping a live panel synchronized with workbook state.
Static HTML dropdowns are fine for fixed lists, but real-world applications need dynamic options — pulled from a sheet's data, a PivotTable field, or a database. Here's how to repopulate a <select> element from VBA after the panel loads:
Public Sub PopulatePeriodDropdown()
On Error Exit Sub
Dim doc As Object
Set doc = wbPanel.Document
If doc Is Nothing Then Exit Sub
' Get the select element
Dim sel As Object
Set sel = doc.getElementById("selPeriod")
If sel Is Nothing Then Exit Sub
' Clear existing options (keep the first placeholder)
Do While sel.Length > 1
sel.Remove 1
Loop
' Read unique periods from the RawData sheet, column B
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("RawData")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
' Collect unique values using a Collection
Dim periods As New Collection
Dim seen As Object
Set seen = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 2 To lastRow
Dim val As String
val = CStr(ws.Cells(i, 2).Value)
If val <> "" And Not seen.Exists(val) Then
seen.Add val, val
periods.Add val
End If
Next i
' Add each unique period as an option
Dim opt As Object
Dim p As Variant
For Each p In periods
Set opt = doc.createElement("option")
opt.Value = p
opt.Text = p
sel.appendChild opt
Next p
End Sub
Call PopulatePeriodDropdown from UserForm_Initialize after the page finishes loading:
Private Sub wbPanel_DocumentComplete(ByVal pDisp As Object, URL As Variant)
' This fires when the HTML document finishes loading
PopulatePeriodDropdown
UpdateKPIDisplay
End Sub
The DocumentComplete event on the WebBrowser control is your signal that the DOM is ready. Trying to manipulate the DOM before this fires will cause silent failures — always wait for this event.
Warning: There's a subtle timing issue here.
wbPanel_DocumentCompletefires for every navigation, including frame loads. If your HTML has no frames, you're fine. But if you embed any external resources (images, fonts), this event may fire multiple times. A safe guard is to checkURLagainst your expected temp file path before running DOM setup code.
Now let's put it all together with a complete exercise you can build against a realistic workbook.
Setup: Create a workbook with these sheets:
Dashboard — a summary sheet with named ranges KPI_Revenue, KPI_Margin, KPI_VarianceCountSalesData — transaction data with columns: Date (A), Period (B), Region (C), Product (D), Revenue (E), Cost (F)PivotReport — a PivotTable summarizing SalesDataPopulate SalesData with at least 200 rows of realistic-looking data. If you don't have data, use this quick generator in a module:
Sub GenerateSampleData()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("SalesData")
ws.Cells.Clear
ws.Range("A1:F1").Value = Array("Date", "Period", "Region", "Product", "Revenue", "Cost")
Dim regions As Variant: regions = Array("North", "South", "East", "West")
Dim products As Variant: products = Array("Widget A", "Widget B", "Service X", "Consulting")
Dim quarters As Variant: quarters = Array("Q1-2025", "Q2-2025", "Q3-2025", "Q4-2025")
Dim i As Long
For i = 2 To 201
ws.Cells(i, 1).Value = DateSerial(2025, Int((i - 2) / 17) + 1, (i Mod 28) + 1)
ws.Cells(i, 2).Value = quarters(Int((i - 2) / 50))
ws.Cells(i, 3).Value = regions(i Mod 4)
ws.Cells(i, 4).Value = products(i Mod 4)
ws.Cells(i, 5).Value = 5000 + (i * 137 Mod 8000)
ws.Cells(i, 6).Value = ws.Cells(i, 5).Value * (0.55 + (i Mod 20) / 100)
Next i
ws.Columns("A:F").AutoFit
MsgBox "Sample data created: 200 rows"
End Sub
Exercise objectives:
TaskPaneForm using ShowTaskPaneSalesDataPivotReport in the HTML without breaking the existing buttonsRefreshPivot that refreshes the PivotTable on the PivotReport sheet, and add a corresponding button to the Actions sectionFor step 5, the VBA handler looks like this:
Private Sub RefreshPivot()
On Error GoTo PivotError
SetPaneStatus "Refreshing pivot...", "warn"
DoEvents
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("PivotReport")
Dim pt As PivotTable
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
SetPaneStatus "Pivot refreshed", "ok"
Exit Sub
PivotError:
SetPaneStatus "Pivot error: " & Err.Description, "err"
End Sub
This integrates naturally with Automating Excel PivotTables with VBA: Create, Refresh, and Filter PivotTables Programmatically if you want to go deeper on programmatic PivotTable control.
Cause: The window.external.RunVBA call fails silently because RunVBA isn't declared as Public Sub in the form's code module, or the form's code is in a standard module.
Fix: Open the form's code module directly (double-click the form in Project Explorer, not the module). Make sure Public Sub RunVBA(...) is defined there.
Cause: You're calling DOM-manipulation code before DocumentComplete fires.
Fix: Always put DOM-update code inside wbPanel_DocumentComplete or call it only after the event has fired. Add a module-level boolean Private paneLoaded As Boolean, set it to True in DocumentComplete, and guard your update procedures with If Not paneLoaded Then Exit Sub.
Cause: You used taskPane.Show without vbModeless, or ShowModal is still True in the form's properties.
Fix: Change ShowModal to False in form properties, and always open with taskPane.Show vbModeless.
Cause: The IE/Trident engine has a compatibility mode quirk. Without a DOCTYPE and meta charset, it falls back to quirks mode and ignores many modern CSS properties.
Fix: Make sure your HTML starts with exactly <!DOCTYPE html><html><head><meta charset='UTF-8'>. This forces standards mode in the embedded browser engine.
Cause: vbModeless forms are attached to the Excel application, and the form's positioning logic ran at launch time. If the user switches workbooks, the form may move or vanish.
Fix: Add a handler in ThisWorkbook_WindowActivate to re-show and reposition the pane:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If Not taskPane Is Nothing Then
If taskPane.Visible Then
taskPane.SetFocus
End If
End If
End Sub
Cause: If a named range is empty or the sheet doesn't exist, ws.Range("KPI_Revenue").Value raises an error.
Fix: Use defensive error handling in UpdateKPIDisplay. The lesson on Error Handling and Debugging VBA Code Like a Pro covers the structured On Error patterns you should apply here. A Resume Next with null-checks is the right pattern for non-critical display updates.
Tip: While building and debugging, open the browser's developer tools equivalent by right-clicking inside the WebBrowser control and choosing "View Source" or navigating to the temp HTML file in a regular browser. This lets you inspect your HTML, verify the CSS, and test JavaScript calls directly — much faster than the VBA Edit-Compile-Run cycle.
Once your basic task pane works, these patterns make it production-worthy.
Store the user's last-selected filter values in a hidden sheet so they persist across sessions:
Private Sub SavePaneState()
Dim stateSheet As Worksheet
' Use a hidden "Config" sheet to persist settings
On Error Resume Next
Set stateSheet = ThisWorkbook.Worksheets("_Config")
On Error GoTo 0
If stateSheet Is Nothing Then
Set stateSheet = ThisWorkbook.Worksheets.Add
stateSheet.Name = "_Config"
stateSheet.Visible = xlSheetVeryHidden
End If
Dim doc As Object
Set doc = wbPanel.Document
If doc Is Nothing Then Exit Sub
stateSheet.Range("A1").Value = doc.getElementById("selPeriod").Value
stateSheet.Range("A2").Value = doc.getElementById("selDepartment").Value
End Sub
Replace the hardcoded #1e3a5f navy color with a value read from your workbook's color scheme. Store a hex value in a named range (BrandColor) and inject it into the HTML string before writing the temp file:
Dim brandColor As String
brandColor = ThisWorkbook.Names("BrandColor").RefersToRange.Value
html = Replace(html, "#1e3a5f", brandColor)
Once you're happy with the task pane, convert the workbook to an .xlam add-in so any workbook can access it. This is covered in detail in Building Excel Add-Ins with VBA: Package and Deploy Custom Tools Across Your Organization — the principles of exposing public procedures and managing state across workbooks apply directly to this pane architecture.
You've built a genuine interactive task pane from VBA and HTML — not a workaround, but a proper application interface that sits alongside your spreadsheet and gives users a professional control surface. The core patterns you've mastered:
WebBrowser controlwindow.external.RunVBA routing through a public dispatch methodwbPanel.Document.getElementByIdFrom here, the natural progression depends on what you're building:
The task pane approach transforms workbooks from spreadsheets into applications. That's a meaningful shift — not just aesthetically, but in how users trust, navigate, and rely on the tools you build.