Create Interactive Dashboard with UserForms, Excel VBA

Creating an interactive dashboard in Excel using VBA with UserForms can be a powerful way to present data, enabling user interaction and dynamic visualizations. Below is a detailed step-by-step guide and VBA code that shows how to build such a dashboard.

Steps Overview:

1. Prepare Your Excel Data: Ensure your data is organized in a structured format, such as a table or range, to make it easier for the VBA code to access and process it.
2. Create the UserForm: The UserForm is where users will interact with the dashboard. You can add buttons, combo boxes, labels, etc., to allow users to filter or manipulate the data.
3. Add VBA Code: The code behind the UserForm will handle data filtering, chart creation, and updates based on user interactions.
4. Build Charts Dynamically: Based on user input, the VBA code will update charts, pivot tables, and other elements on the dashboard.

Step-by-Step Example

1. Prepare the Data:

Let’s assume you have sales data in a worksheet like this:

2. Create the UserForm:

• Go to the VBA Editor by pressing Alt + F11.
• Insert a new UserForm:
  • In the Project Explorer, right-click and select Insert > UserForm.
• Add the following controls:
  • ComboBox (cmbRegion) for selecting the region.
  • CommandButton (cmdShowData) to show the filtered data.
  • ChartObject to display the chart dynamically.
  • Label (lblTitle) for the title of the dashboard.

3. VBA Code:

' Module code for initializing UserForm and generating chart
Sub ShowDashboard()
    ' Create and show the UserForm
    UserForm1.Show
End Sub

' Code behind UserForm
Private Sub UserForm_Initialize()
    ' Populate ComboBox with unique regions
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SalesData") ' Modify the sheet name if needed   
    Dim regionRange As Range
    Set regionRange = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, 2).End(xlUp).Row) ' Adjust for your data range
    Dim cell As Range
    Dim regionList As Collection
    Set regionList = New Collection
    On Error Resume Next
    For Each cell In regionRange
        regionList.Add cell.Value, CStr(cell.Value)
    Next cell
    On Error GoTo 0
    ' Fill the ComboBox with unique region names
    For Each Item In regionList
        cmbRegion.AddItem Item
    Next Item
    ' Set default selection
    cmbRegion.ListIndex = 0
End Sub

Private Sub cmdShowData_Click()
    ' Filter data based on ComboBox selection
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SalesData")   
    Dim selectedRegion As String
    selectedRegion = cmbRegion.Value
    Dim dataRange As Range
    Set dataRange = ws.Range("A1:C" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row) ' Data range
    ' Clear existing chart
    On Error Resume Next
    Me.ChartObjects("SalesChart").Delete
    On Error GoTo 0   
    ' Filter data and create chart dynamically
    Dim filteredData As Range
    Set filteredData = ws.Range("A1:C1").Resize(1, 3) ' Header row
    Dim r As Range
    For Each r In dataRange.Rows
        If r.Cells(2).Value = selectedRegion Then
            Set filteredData = Union(filteredData, r)
        End If
    Next r   
    ' Create a new chart based on filtered data
    Dim chart As ChartObject
    Set chart = Me.ChartObjects.Add(Left:=100, Width:=400, Top:=200, Height:=300)
    chart.Name = "SalesChart"
    chart.Chart.SetSourceData Source:=filteredData
    chart.Chart.ChartType = xlColumnClustered
    chart.Chart.HasTitle = True
    chart.Chart.ChartTitle.Text = "Sales Data for " & selectedRegion   
    ' Update title
    lblTitle.Caption = "Sales Dashboard - " & selectedRegion
End Sub

4. Explanation of Code:

• Initialize the UserForm:
  • The UserForm_Initialize subroutine populates the ComboBox with unique region names from the dataset.
  • It uses a Collection to store unique region values to avoid duplicates.
• Handling User Interaction:
  • When the user selects a region and clicks the Show Data button (cmdShowData), the cmdShowData_Click subroutine is triggered.
  • This subroutine filters the data based on the selected region from the ComboBox.
  • It creates a new chart dynamically, setting its data source to the filtered data and adjusting its type (e.g., a clustered column chart).
  • It updates the chart’s title and the dashboard title accordingly.
• Dynamic Chart Creation:
  • The chart is created using ChartObjects.Add and then customized (size, title, and type).
  • The data range is adjusted dynamically based on user selection.
• UserForm Layout:
  • The ComboBox allows the user to select a region.
  • The CommandButton triggers the display of the filtered data and chart.
  • The Label (lblTitle) shows the title, which updates when a region is selected.

5. Final Notes:

• Customizing the Data: You can modify the data filtering logic in cmdShowData_Click to apply additional filters, such as date ranges or other criteria.
• Multiple Charts: You can add multiple chart types or other visualizations by creating new ChartObjects or PivotTables.
• Improving the Layout: You can further enhance the UserForm with additional controls like slicers, more ComboBoxes, or data input fields.

6. Running the Dashboard:

• Once the code is in place, simply run the ShowDashboard macro (you can assign it to a button or run it manually) to open the UserForm. The dashboard will update dynamically based on user inputs.

This approach provides a simple yet flexible way to create an interactive Excel dashboard with VBA. You can expand it by adding more features like advanced filtering, multiple charts, or even incorporating external data sources.