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.
Since my source data is in A2:D8 on Source Datasheet. I use the below formula.
|=VLOOKUP(C2,'Source Data'!$A$2:$D$8,MATCH($D$1,'Source Data'!$A$1:$D$1,0))|
Here we are using dynamic column indexing for VLOOKUP. You can read about it here.
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.
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.
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.
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.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.