Interactice Dashboard

In Excel ChartObjects don't have events. But they can be assigned to a class object which can be endowed with the capability to respond to events. If this sounds complicated that is so because it is. But it's not voluminous and complicated things become easy when digested in small doses. Here goes.

If you want a Class the logical first step is to create one. Create a class module in your project and give it a meaningful name. I named it ChartClass. At the top of this new module declare a variable. I named it MyChart.

Private WithEvents MyChart As Chart

Observe that this declaration instructs Excel to give any chart object assigned to it access to the class events. Also observe that this variable is Private, meaning it is accessible only within this class module, but to every procedure there. Of course it's empty. Therefore we give it a value next.

Private Sub Class_Initialize()

    ' only the first chart on 'Dashboard' will respond
    Set MyChart = Worksheets("Dashboard").ChartObjects(1).Chart
End Sub 

The above procedure limits the scope of the entire class to the single chart on your Dashboard. If ever needed this can be re-designed to make the variable MyChart truly variable. For now just note that the Class_Initialize procedure will run automatically whenever the class is initialized - just like a UserForm's Initialize events.

In order to trigger the Class_Initialize event we need an instance of the class. Such an instance can't be created by VBA unless we have a place to keep it at. So, we create a variable for just this purpose. I called it Echart (like Event_Chart).

Dim Echart As ChartClass

Observe that the data type is ChartClass, which is the name I gave to the class. Pay especial attention to the location of this declaration. It can't be in the class module because it can't be of the data type defined by itself. The best place to keep it is in a standard code module. In the attached workbook I placed it in the ThisWorkbook module which will do just as well. Observe that a Dim statement outside a procedure must be at the top of the sheet, before any procedures and makes the variable Public unless it's declared as Private explicitly. Echart will be available throughout the project.

Of course Echart is empty as yet. So we take care of that next. We need just one line Set Echart = New ChartClass. This will assign a new instance of our ChartClass to the variable Echart, and that will trigger the Class_Initialize event linking Echart to MyChart in the class module, which is linked to your chart on the Dashboard tab. The question is where to put it. I opted for the Workbook_Open event.

This code must be in the ThisWorkbook module. No other location will do. It will fire when you open the workbook, creating an instance of the ChartClass fully automatic. Note that the variable Echart will lose its assigned value when you add code to your project, do code modifications, or when a VBA crash occurs. You can run the Worksheet_Open procedure with F5 to restore Echart's value. No need to close and re-open the workbook just to run this event procedure.

Private Sub Workbook_Open()
    Set Echart = New ChartClass  
End Sub

Now you are all set. You can capture all the class events listed in the right-hand drop-down of the class module with MyChart selected on the left. For your plan you need the Select event. You want a procedure to run when your chart is selected. Here it is. Paste it below the existing in the class module.

Private Sub MyChart_Select(ByVal ElementID As Long, _
                            ByVal Arg1 As Long, _
                            ByVal Arg2 As Long)
     Dim Ws As Worksheet     On Error Resume Next
                    ' in case you rename the sheet
     Set Ws = Worksheets("Source Data")
     If Err = 0 Then
         Ws.Activate
         Ws.Cells(2, 2).Select
         ActiveWindow.FreezePanes = True
     End If
 End Sub

Please take a close look at this code. It not only activates the Source Data worksheet but also select B2 and freezes the panes above it and to its left. Remove that part of the code if you don't like its effect.

To round your idea off, you need a way to get back from the data to your chart. I thought it would be a nice gesture to throw this in as a bonus. Copy this code to the code sheet of the Source Data tab (nowhere else it can work).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)

    If Target.Address = Range("A1").Address Then
        ' prevent the Class event from firing
        Application.EnableEvents = False

        With Worksheets("Dashboard")
            .Activate
            .ChartObjects(1).Select
        End With

        Application.EnableEvents = True
        Cancel = True
    End If 

Now a double-click on A1 will take you back to your Dashboard and your chart. All of the above is implemented in the attached workbook.