VBA editor basics in Excel - Dashboard Guide

Dashboard Mode - VBA editor basics

Dashboard Goal

Learn the basics of the VBA editor in Excel to write and run simple macros that automate tasks.

Sample Data

Task	Status	Due Date
Prepare report	Pending	2024-06-10
Send emails	Completed	2024-06-05
Update database	Pending	2024-06-12
Backup files	Completed	2024-06-01
Schedule meeting	Pending	2024-06-15

Dashboard Components

• KPI Card: Count of Pending Tasks
  Formula: =COUNTIF(B2:B6,"Pending")
  Result: 3
• KPI Card: Count of Completed Tasks
  Formula: =COUNTIF(B2:B6,"Completed")
  Result: 2
• Button: Runs a simple VBA macro to mark all Pending tasks as Completed.
  VBA Code:

  Sub MarkPendingCompleted()
    Dim cell As Range
    For Each cell In Range("B2:B6")
      If cell.Value = "Pending" Then
        cell.Value = "Completed"
      End If
    Next cell
  End Sub

• Instruction Text: Steps to open VBA editor:
  1. Press Alt + F11 to open the VBA editor.
  2. Insert a new module.
  3. Paste the macro code.
  4. Run the macro or assign it to a button.

Dashboard Layout

+----------------------+----------------------+
|  KPI: Pending Tasks   |  KPI: Completed Tasks |
|       (3)            |        (2)            |
+----------------------+----------------------+
|                  Button: Run Macro             |
+------------------------------------------------+
|           Instruction: How to use VBA Editor    |
|  1. Alt + F11 to open editor                     |
|  2. Insert Module                                |
|  3. Paste code                                  |
|  4. Run macro or assign button                   |
+------------------------------------------------+

Interactivity

Clicking the Run Macro button executes the VBA code that changes all "Pending" statuses to "Completed" in the data table. This updates the KPI cards automatically because they count the statuses live.

Self Check

After running the macro, what happens to the count of Pending and Completed tasks in the KPI cards?

Answer: Pending tasks count changes from 3 to 0, Completed tasks count changes from 2 to 5.

Key Result

A simple dashboard showing task status counts and a button to run a VBA macro that updates task statuses.