DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How to Change Chart Data as Per Selected Cell in Excel VBA?

#tochangechartdataasp #vbamacros

In this article, you will learn how to change chart data as per selected cell In Excel VBA. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

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

  • Firstly, you have to create a sample data from different regions in a sheet.
  • Now, you need to name it as source data.

Source data

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

  • You have to insert a new sheet and name it as “Dashboard”.
  • Then, you need to copy all months in one column.
  • Now, you have to write one region’s name adjacent to the month.

Dashboard

  • After that, you need to pull data of the region in Cell B1.
  • You have to use the following formula given below. 
 =VLOOKUP(C2,'Source Data'!$A$2:$D$8,MATCH($D$1,'Source Data'!$A$1:$D$1,0))
Enter fullscreen mode Exit fullscreen mode
  • Then, you need to insert A chart using this data on the Dashboard sheet.
  • Now, you have to click on the chart icon and select line chart.

Line Chart

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

  • Firstly, you need to write all region’s names in a range,and write them in range A2:A4.
  • You have to right-click on the Dashboard sheet name.

Click on Dashboard Name

  • Then, you need to click on the View Code option to enter directly into Worksheet Module in VBE so that we can use the worksheet event.
  • Now, you have to write the following code given below in the 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
Enter fullscreen mode Exit fullscreen mode
  • After that, you need to save the code by selecting it and then close the window.

Save the Code

  • Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
  • You need to choose the Macros option in the Code section.

Macro Option

  • After that, you need to select a cell in range A2:A4 , and it’s value will be assigned to D1 and the data of the chart will change accordingly.
  • Finally, you will receive output as given below.

Data of the chart

A Short Summary

In this tutorial, we guided you on how to change chart data as per the selected cell In Excel VBA. Leave your queries/suggestions in the below comment section. Thanks for visiting Geek Excel. Keep Learning!

Keep Reading:

Top comments (0)