• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Charts

Hi ,

It would help if you could say where you find difficulty in understanding the tutorial.

Broadly , what is happening is :

1. When you make your selection of two items from the left side and the right side , the following event procedure is executed :

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Value = "" Then Exit Sub
    If Not (Application.Intersect(ActiveCell, Range("rngSel1").Cells) Is Nothing) Then
        Call setOption1
    ElseIf Not (Application.Intersect(ActiveCell, Range("rngSel2").Cells) Is Nothing) Then
        Call setOption2
    End If
End Sub
The two procedures which are called are setOption1 and setOption2.

These two procedures are :
Code:
Sub setOption1()
    Dim topRow As Integer
   
    topRow = Range("rngSel1").Cells(1, 1).Row
    [valOption1] = ActiveCell.Row() - topRow + 1
End Sub
Sub setOption2()
    Dim topRow As Integer
   
    topRow = Range("rngSel2").Cells(1, 1).Row
    [valOption2] = ActiveCell.Row() - topRow + 1
End Sub
What these two procedures are doing is to set the named ranges valOption1 and valOption2.

The charts themselves are pre-created on the calcs tab , in the range B72 through I34 ; the dashboard contains a picture placeholder Picture 4 , which has the formula =selChart.

selChart is a named range , which has the following formula in its Refers To box :

=CHOOSE(valChartToDisplay,calcs!$C$73:$H$81, calcs!$C$84:$H$92, calcs!$C$95:$H$103, calcs!$C$114:$H$122, calcs!$C$125:$H$133)

What this does , therefore , is that depending on the value ( between 1 and 5 ) of the named range valChartToDisplay , it chooses which of the 5 charts will be displayed in the picture placeholder.

valChartToDisplay itself refers to =calcs!$H$4 ; this is the linked cell of the Drop down 4. When you change the selection in the drop down , the value in valChartToDisplay changes in the range 1 through 5.

Thus , the 5 charts are based on the data in static columns D and E , G and H , J and K , M and N , P and Q in the ranges $D$10:$D$37 and $E$10:$E$37 , $G$10:$G$37 and $H$10:$H37 , and so on.

Thus the 5 charts themselves are absolutely static ; nothing in these charts changes except the data.

The data itself changes because of the formulae in the cells in rows 10 through 37 ; depending on the selection made in the left side and the right side ( within the named ranges rngSel1 and rngSel2 ) , the titles in row 9 on the calcs tab change. Since the cells D9 , E9 , G9 , H9 ,... are used in the formulae , the data changes based on the selection made.

Drop down 4 decides which of the 5 charts will be displayed.

Narayan
 
Back
Top