Simple VBA procedures in Excel - Dashboard Guide

Dashboard Mode - Simple VBA procedures

Dashboard Goal

Show how to use simple VBA procedures to automate tasks in Excel, like clearing data and adding a timestamp.

Sample Data

Task	Status	Last Updated
Prepare Report	Done	2024-06-01 09:00
Send Email	Pending	2024-06-01 10:30
Update Sheet	In Progress	2024-06-01 11:15
Review Data	Done	2024-06-01 12:00
Backup File	Pending	2024-06-01 13:45

Dashboard Components

• KPI Card: Count of tasks with status 'Done' using formula =COUNTIF(B2:B6, "Done"). Shows how many tasks are completed.
• Button 1: Clear Status - VBA procedure clears the 'Status' column (B2:B6) to empty.
• Button 2: Add Timestamp - VBA procedure adds current date and time to 'Last Updated' column (C2:C6) for all rows.
• Table: Shows the task list with columns Task, Status, and Last Updated.

VBA Code Snippets

Sub ClearStatus()
  Range("B2:B6").ClearContents
End Sub

Sub AddTimestamp()
  Dim cell As Range
  For Each cell In Range("C2:C6")
    cell.Value = Now()
  Next cell
End Sub

Dashboard Layout

+----------------------+---------------------+
|      KPI Card        |     Buttons Panel    |
|  (Count Done Tasks)  |  [Clear Status]      |
|                      |  [Add Timestamp]     |
+----------------------+---------------------+
|                  Task List Table               |
|  Task  |  Status  |  Last Updated               |
|-----------------------------------------------|
|  ...   |   ...    |    ...                     |
+------------------------------------------------+

Interactivity

Clicking Clear Status button runs the ClearStatus VBA procedure which empties the Status column. The KPI card updates automatically to show zero done tasks.

Clicking Add Timestamp button runs the AddTimestamp VBA procedure which fills the Last Updated column with the current date and time for all tasks.

Self Check

If you click the Clear Status button, what happens to the KPI card count?

Answer: The KPI card count changes to 0 because all statuses are cleared, so no tasks are marked 'Done'.

Key Result

Dashboard demonstrating simple VBA procedures to clear status and add timestamps with live KPI update.