Create Kanban Board with Excel VBA

To create a Kanban board in Excel using VBA, you’ll need to set up different columns representing the stages of your workflow (e.g., « To Do, » « In Progress, » and « Done »). Each task will be represented by a row or a cell, and you can drag tasks between columns as they progress.

Here’s a step-by-step guide with detailed explanations for creating a simple Kanban board:

Step 1: Set up your Excel Sheet Layout

1. Create Columns: Set up columns for different stages of your Kanban process, for example:
   • Column A: « Task Name »
   • Column B: « To Do »
   • Column C: « In Progress »
   • Column D: « Done »
2. Task Data: Each row will represent a task, and tasks will move between the columns based on their progress.
3. Cell Formatting: You can format the columns with background colors to differentiate the stages.

Step 2: Set Up a VBA Module to Create Kanban Logic

Below is a detailed VBA code to create a simple Kanban board with the ability to move tasks between columns by clicking a button.

Sub CreateKanbanBoard()
    ' Set up initial columns
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "Kanban Board"   
    ' Set up the Kanban board headers
    ws.Cells(1, 1).Value = "Task Name"
    ws.Cells(1, 2).Value = "To Do"
    ws.Cells(1, 3).Value = "In Progress"
    ws.Cells(1, 4).Value = "Done"   
    ' Formatting headers
    ws.Rows(1).Font.Bold = True
    ws.Rows(1).Interior.Color = RGB(0, 102, 204) ' Blue background for headers
    ws.Rows(1).Font.Color = RGB(255, 255, 255) ' White font color
    ' Format columns for better visibility
    ws.Columns("A:D").AutoFit
    ws.Columns("A:A").ColumnWidth = 20
    ws.Columns("B:D").ColumnWidth = 15
    ' Example Tasks (to be added in To Do section)
    ws.Cells(2, 1).Value = "Task 1"
    ws.Cells(3, 1).Value = "Task 2"
    ws.Cells(4, 1).Value = "Task 3"
    ' Insert buttons for moving tasks
    InsertKanbanButtons ws
End Sub

Sub InsertKanbanButtons(ws As Worksheet)
    ' Create a button to move tasks between columns   
    ' Button to move Task to "In Progress"
    Dim btnInProgress As Button
    Set btnInProgress = ws.Buttons.Add(Left:=ws.Cells(2, 2).Left, Top:=ws.Cells(2, 2).Top, Width:=100, Height:=30)
    btnInProgress.OnAction = "MoveToInProgress"
    btnInProgress.Caption = "Move to In Progress"   
    ' Button to move Task to "Done"
    Dim btnDone As Button
    Set btnDone = ws.Buttons.Add(Left:=ws.Cells(2, 3).Left, Top:=ws.Cells(2, 3).Top, Width:=100, Height:=30)
    btnDone.OnAction = "MoveToDone"
    btnDone.Caption = "Move to Done"   
    ' Button to move Task back to "To Do"
    Dim btnBackToDo As Button
    Set btnBackToDo = ws.Buttons.Add(Left:=ws.Cells(2, 4).Left, Top:=ws.Cells(2, 4).Top, Width:=100, Height:=30)
    btnBackToDo.OnAction = "MoveBackToDo"
    btnBackToDo.Caption = "Move Back to To Do"
End Sub

Sub MoveToInProgress()
    ' Move the selected task from "To Do" to "In Progress"
    Dim selectedCell As Range
    Set selectedCell = Selection
    If selectedCell.Column = 2 And selectedCell.Value <> "" Then
        selectedCell.Offset(0, 1).Value = selectedCell.Value
        selectedCell.ClearContents
    End If
End Sub

Sub MoveToDone()
    ' Move the selected task from "In Progress" to "Done"
    Dim selectedCell As Range
    Set selectedCell = Selection
    If selectedCell.Column = 3 And selectedCell.Value <> "" Then
        selectedCell.Offset(0, 1).Value = selectedCell.Value
        selectedCell.ClearContents
    End If
End Sub

Sub MoveBackToDo()
    ' Move the selected task from "Done" to "To Do"
    Dim selectedCell As Range
    Set selectedCell = Selection
    If selectedCell.Column = 4 And selectedCell.Value <> "" Then
        selectedCell.Offset(0, -3).Value = selectedCell.Value
        selectedCell.ClearContents
    End If
End Sub

Step 3: Explanation of the Code

1. CreateKanbanBoard:
   • This subroutine creates the worksheet for your Kanban board, sets up the headers (« Task Name, » « To Do, » « In Progress, » and « Done »), and adds example tasks under the « To Do » column.
   • It also formats the headers and columns for better visibility.
2. InsertKanbanButtons:
   • This subroutine adds buttons to each task in the « To Do, » « In Progress, » and « Done » columns to move tasks between the columns.
   • Each button is linked to a specific subroutine (MoveToInProgress, MoveToDone, MoveBackToDo) to move tasks based on their progress.
3. MoveToInProgress, MoveToDone, and MoveBackToDo:
   • These subroutines handle the movement of tasks when a button is clicked. They check if the task is in the correct column and move it to the next one while clearing the original column.

Step 4: How to Use the Kanban Board

1. When you run the CreateKanbanBoard macro, a new sheet will be created with your Kanban board.
2. Add or modify tasks in the « To Do » column.
3. Click the buttons to move tasks to « In Progress » or « Done, » or move them back to « To Do » as needed.

Step 5: Customization Options

• Colors and Formatting: Customize the colors and formatting of the task cells and buttons to suit your preferences.
• Additional Columns: You can add additional columns like « Blocked » or « Review » to represent different stages in your workflow.
• Advanced Features: Consider adding features like filtering tasks, using checkboxes for task completion, or allowing users to add notes for each task.

This is a simple Kanban board setup, but you can expand it by integrating more advanced features, such as due dates, priority labels, or task owners.