Change Chart Data as Per Selected Cell

If you want to make a dashboard with a chart that changes its data as per the selected options, you can use the events in VBA. Yes, it can be done. We won't need any drop-down, slicer, or combo box. We will make cells clickable and change data to create a chart from the selected cell.

Follow the below steps to make dynamic charts in excel that change as per the cell selection.

Step1: Prepare the data in a Sheet as a source for the chart.

Here I have some sample data from different regions in a sheet. I named it source data.

Step 2: Get one region's data at one time on a different sheet.

• Now insert a new sheet. Name it appropriately. I named it as "Dashboard".
• Copy all months in one column. Write one region's name adjacent to the month.
• Now we want to pull data of the region in Cell D1. We want the data to change as the region changes in D1. For that, we can use the Two Way Lookup.

Since my source data is in A2:D8 on Source Datasheet. I use the below formula.

Here we are using dynamic column indexing for VLOOKUP. You can read about it here.

• Insert A chart using this data on the Dashboard sheet. I use a simple line chart. Hide the source of the chart if you don't want to show them.

Now as you change the region name in D1 the chart will change accordingly. The next step is to change the region name in D1 as you select an option from the specified cell.

Step 3: Change the region as you select a region name in the specified range.

• Write all region's names in a range, I write them in range A2:A4.

• Right-click on the Dashboard sheet name and click on the "View Code" option to enter directly into Worksheet Module in VBE so that we can use the worksheet event.
• Now write below code in VB Editor.

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("A2:A4")) Is Nothing Then
    Range("A2:A4").Interior.ColorIndex = xlColorIndexNone 
    Dim region As Variant
    region = Target.value
    On Error GoTo err:
    Select Case region
     Case Is = "Central"
       Range("D1").value = region
     Case Is = "East"
       Range("D1").value = region
     Case Is = "West"
       Range("D1").value = region
     Case Else
       MsgBox "Invalid Option"
  End Select
  Target.Interior.ColorIndex = 8
  End If
  err:
  End Sub
  

And it is done. Now, whenever you will select a cell in range A2:A4, it's value will be assigned to D1 and the data of the chart will change accordingly.

I have explained how this code working below. You can understand it and make changes as per your requirement. I have provided links to help topics that I have used here in this example. So do check them out.

How the code is working?

Here I have used the Event of Excel. I used a worksheet event "SelectionChange" to trigger the events.

If Not Intersect(Target, Range("A2:A4")) Is Nothing Then

This line sets the focus to the range A2:A4 so that the SelectionChange event only fires when the selection is in range A2:A4. The code between If and End will only run if the selection is in range A2:A4. You can now set it as per your requirement to make your chart dynamic.

Range("A2:A4").Interior.ColorIndex = xlColorIndexNone

This line sets the color of range A2:A4 to nothing.

region = Target.value
On Error GoTo err:

In the above two lines, we get the value of the selected cells in the variable region and ignore any error that occurs. don't use the line "On Error GoTo err:" until you are sure that you want to ignore any error that occurs. I used it to avoid an error when I select multiple cells.

Select Case region
   Case Is = "Central"
     Range("D1").value = region
   Case Is = "East"
     Range("D1").value = region
   Case Is = "West"
     Range("D1").value = region
   Case Else
     MsgBox "Invalid Option"
End Select

In the above lines, we are using excels Select Case Statement to set the value of range D1.

Target.Interior.ColorIndex = 8
End If
err:
End Sub

Before the End If statement, we change the color of the selected option so that it gets highlighted. Then If statement ends and err: tag starts. The On Error statement will jump to this tag if any error occurs during the select statement.

Download the working file below.

Related Articles:

Embedded Chart Events Using VBA in Microsoft Excel | The embedded charts events can make your chart more interactive, dynamic, and useful than normal charts. To enable the events on charts we...

The Events in Excel VBA | There are seven types of Events in Excel. Each event deals in different scope. Application Event deals with on workbook level. Workbook on sheets level. Worksheet Event on Range level.

The Worksheet Events in Excel VBA |The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.

Workbook events using VBA in Microsoft Excel | The workbook events work on the entire workbook. Since all the sheets are part of the workbook, these events work on them too.

Prevent an automacro/eventmacro executes using VBA in Microsoft Excel | To prevent the run of auto_open macro use the shift key.

Chart object events using VBA in Microsoft Excel | The Charts are complex objects and there are several components that you attached to them. To make the Chart Events we use the Class module.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.