Excel Macro Control Panel Guide

VBA Automation

Excel Macro Control Panel

Build a dynamic dashboard that scans your workbook, extracts macro names and comments, and creates a clickable index to execute scripts directly from a clean UI.

1

Enable Developer Permissions

Because this dashboard reads the code inside your workbook to build the index, you must give Excel permission to view its own code structure.

• Open your Excel workbook.
• Go to File > Options > Trust Center > Trust Center Settings.
• Click Macro Settings on the left menu.
• Check the box for Trust access to the VBA project object model.
• Click OK on all windows to save.

2

Add the Core Engine Module

This code scans your entire workbook, extracts macro names, reads your code comments to create automatic descriptions, applies zebra styling, and creates the synchronization button.

Crucial Reference Required

Press ALT + F11. Click Tools > References. Check the box for Microsoft Visual Basic for Applications Extensibility 5.3 and click OK.

Click Insert > Module and paste this complete script:

Module1 (Core Engine)

Sub GenerateMacroIndex()
    Dim wsIndex As Worksheet
    Dim vbaProj As VBProject
    Dim vbaComp As VBComponent
    Dim codeMod As CodeModule
    Dim lineNum As Long, rowNum As Long, i As Long
    Dim macroName As String, description As String
    Dim btnRefresh As Shape
    
    Set vbaProj = ActiveWorkbook.VBProject
    
    ' 1. Handle Worksheet Setup & Protection
    On Error Resume Next
    Set wsIndex = Sheets("Index")
    On Error GoTo 0
    
    If wsIndex Is Nothing Then
        Set wsIndex = Sheets.Add(Before:=Sheets(1))
        wsIndex.Name = "Index"
    Else
        wsIndex.Unprotect "vba_safe"
        wsIndex.Cells.Clear
        For Each btnRefresh In wsIndex.Shapes
            btnRefresh.Delete
        Next btnRefresh
    End If
    
    ' Hide gridlines for a clean dashboard UI look
    ActiveWindow.DisplayGridlines = False
    
    ' 2. Layout & Typography Styling
    With wsIndex
        .Cells.Font.Name = "Segoe UI"
        .Cells.Font.Size = 10
        
        ' Title block
        .Range("A2").Value = "🚀 EXCEL MACRO CONTROL PANEL"
        .Range("A2").Font.Size = 16
        .Range("A2").Font.Bold = True
        .Range("A2").Font.Color = RGB(44, 62, 80)
        
        .Range("A3").Value = "Click any 'Run' link to execute. Click Refresh to sync workbook structural changes."
        .Range("A3").Font.Italic = True
        .Range("A3").Font.Color = RGB(127, 140, 141)
        
        ' Table Headers
        .Range("A5").Value = "Macro Action"
        .Range("B5").Value = "Macro Name"
        .Range("C5").Value = "Source Component"
        .Range("D5").Value = "Functional Description"
        .Range("E5").Value = "Last Run Timestamp"
        
        With .Range("A5:E5")
            .Font.Bold = True
            .Font.Color = RGB(255, 255, 255)
            .Interior.Color = RGB(44, 62, 80)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .RowHeight = 26
        End With
    End With
    
    rowNum = 6
    
    ' 3. Extract Component Macros & Inline Comments
    For Each vbaComp In vbaProj.VBComponents
        Set codeMod = vbaComp.CodeModule
        lineNum = 1
        
        Do While lineNum < codeMod.CountOfLines
            macroName = codeMod.ProcOfLine(lineNum, vbext_pk_Proc)
            
            ' Filter out internal configurations and infrastructure macros
            If macroName <> "" And macroName <> "GenerateMacroIndex" And macroName <> "RunSelectedMacro" _
               And Not macroName Like "*_SelectionChange" And Not macroName Like "Workbook_*" Then
                
                ' Default description if no comments are found
                description = "No documentation provided."
                
                ' Look for comment lines immediately following the Sub declaration
                Dim procStartLine As Long, procBodyLine As Long
                procStartLine = codeMod.ProcStartLine(macroName, vbext_pk_Proc)
                procBodyLine = codeMod.ProcBodyLine(macroName, vbext_pk_Proc)
                
                ' Check if next text lines contain single quotes or 'Rem'
                For i = procBodyLine + 1 To codeMod.CountOfLines
                    Dim lineText As String
                    lineText = Trim(codeMod.Lines(i, 1))
                    If Left(lineText, 1) = "'" Then
                        description = Mid(lineText, 2)
                        Exit For
                    ElseIf Left(lineText, 4) = "Rem " Then
                        description = Mid(lineText, 5)
                        Exit For
                    ElseIf lineText <> "" Then
                        Exit For ' Stop searching if a real code string starts
                    End If
                Next i
                
                ' Populate the Dashboard Row
                With wsIndex
                    ' Run Action Link
                    .Cells(rowNum, 1).Value = "▶ Run Macro"
                    .Cells(rowNum, 1).Font.Bold = True
                    .Cells(rowNum, 1).Font.Color = RGB(22, 160, 133)
                    .Cells(rowNum, 1).Font.Underline = xlUnderlineStyleSingle
                    .Cells(rowNum, 1).HorizontalAlignment = xlCenter
                    
                    ' Metadata
                    .Cells(rowNum, 2).Value = macroName
                    .Cells(rowNum, 2).Font.Bold = True
                    .Cells(rowNum, 3).Value = vbaComp.Name
                    .Cells(rowNum, 3).HorizontalAlignment = xlCenter
                    .Cells(rowNum, 4).Value = Trim(description)
                    .Cells(rowNum, 5).Value = "Never"
                    .Cells(rowNum, 5).HorizontalAlignment = xlCenter
                    .Cells(rowNum, 5).Font.Color = RGB(149, 165, 166)
                    
                    ' Aesthetic Zebra Striping & Borders
                    With .Range(.Cells(rowNum, 1), .Cells(rowNum, 5))
                        .Borders(xlEdgeBottom).LineStyle = xlContinuous
                        .Borders(xlEdgeBottom).Color = RGB(220, 224, 230)
                        .RowHeight = 22
                        If rowNum Mod 2 = 0 Then
                            .Interior.Color = RGB(248, 249, 250)
                        Else
                            .Interior.Color = RGB(255, 255, 255)
                        End If
                    End With
                End With
                
                rowNum = rowNum + 1
                lineNum = lineNum + codeMod.ProcCountLines(macroName, vbext_pk_Proc)
            Else
                lineNum = lineNum + 1
            End If
        Loop
    Next vbaComp
    
    ' 4. Generate Clean Refresh Button Layout
    Set btnRefresh = wsIndex.Shapes.AddShape(msoShapeRoundedRectangle, 450, 12, 140, 28)
    With btnRefresh
        .TextFrame.Characters.Text = "🔄 Sync Workbook"
        .TextFrame.Characters.Font.Size = 10
        .TextFrame.Characters.Font.Bold = True
        .TextFrame.HorizontalAnchor = msoAnchorCenter
        .TextFrame.VerticalAnchor = msoAnchorMiddle
        .Fill.ForeColor.RGB = RGB(41, 128, 185)
        .Line.Visible = msoFalse
        .OnAction = "GenerateMacroIndex"
    End With
    
    ' Auto alignment cleanups
    wsIndex.Columns("A:E").AutoFit
    wsIndex.Columns("A").ColumnWidth = 15
    wsIndex.Columns("B").ColumnWidth = 25
    wsIndex.Columns("D").ColumnWidth = 45
    wsIndex.Columns("E").ColumnWidth = 22
    
    ' Finalizing Worksheet Locks
    wsIndex.Range("A5:E" & rowNum).Locked = True
    wsIndex.Protect "vba_safe", AllowFormattingColumns:=True
End Sub

' Intermediary execution framework updating time sheets safely
Sub RunSelectedMacro(macroName As String, targetRow As Long)
    Dim wsIndex As Worksheet
    Set wsIndex = Sheets("Index")
    
    On Error Resume Next
    Application.Run macroName
    
    If Err.Number = 0 Then
        ' Update execution status clock cleanly
        wsIndex.Unprotect "vba_safe"
        wsIndex.Cells(targetRow, 5).Value = Format(Now, "yyyy-mm-dd hh:mm:ss")
        wsIndex.Cells(targetRow, 5).Font.Color = RGB(39, 174, 96)
        wsIndex.Cells(targetRow, 5).Font.Bold = True
        wsIndex.Protect "vba_safe"
    Else
        MsgBox "Failed to execute macro: " & macroName, vbCritical, "Execution Error"
    End If
    On Error GoTo 0
End Sub

3

Add the Selection Action Listener

This script lives behind the sheet structure itself. It listens for whenever you click the text links in column A to launch the matching macro.

• In the VBA Editor, look at the left sidebar panel named Project Explorer.
• Look for the sheet item that will act as your dashboard sheet (e.g., Sheet1). Double-click it.
• Paste this event listener code into that window:

Sheet Object (Listener)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Guard condition: Click must be inside Column A, row 6 or deeper, and a single cell selection
    If Target.Cells.Count = 1 And Target.Column = 1 And Target.Row >= 6 Then
        If Target.Value = "▶ Run Macro" Then
            Dim macroToRun As String
            Dim rowIdx As Long
            
            rowIdx = Target.Row
            macroToRun = Target.Offset(0, 1).Value ' Grabs macro string text from column B
            
            ' Shift focus sideways slightly to prevent looping bugs
            Target.Offset(0, 1).Select
            
            ' Safely pass execution to engine along with row indices for real-time tracking
            Application.OnTime Now, "'RunSelectedMacro """ & macroToRun & """, " & rowIdx & "'"
        End If
    End If
End Sub

Close the VBA Editor window and return to Excel.

4

Run and Test

1. On your normal Excel window, press ALT + F8.
2. Select GenerateMacroIndex from the popup menu list and click Run.
3. A beautifully formatted dashboard sheet named "Index" will instantly appear.
4. Click ▶ Run Macro next to any item to test it. The last column will instantly change from "Never" to a green live execution timestamp.

Important: Make sure to save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep this functional dashboard saved!