CODEWITHSUNDEEP
excelvbacharts
NewSpeaker
NewSpeaker

Reputation: 67

VBA - Make chart B appear when series 1 of chart A is selected

I have a chart with 14 series. Likewise I have 14 charts that are associated with each of these series. I am trying to create a macro that can be called when a particular series is selected. When the series is selected the macro makes the corresponding chart appear which gives the user more detailed data.

This is what I have come up with so far (Bear with me I am a weak programmer). Please let me know if I am on he right track and if not please give me some direction. this code currently gives me an error message "Object doesn't support this property or method".

Thanks!

Sub Macro1()

Dim Series6 As Object
Set Series6 = ActiveChart.SeriesCollection(1).Points(6)

   If Series6.Select Then
   Sheets("Sheet1").ChartObjects("Chart 2").Visible = True
   End If
End Sub

Upvotes: 1

Views: 1843

Answers (1)

chris neilsen
chris neilsen

Reputation: 53137

You can create Chart Events to handle this type of thing

Assumptions:

  • Charts are on Sheet1
  • Master chart is named Chart 1
  • Detail charts are named after the series number +1 (eg Series 1 relates to Chart 2)

To set up chart events follow these steps

  1. Create a Class Module named EventClassModule

  2. Add code to this module

    Option Explicit

Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Dim ChartName As String
    Dim i As Long
    If ElementID = 3 And Arg1 = 1 Then ' 3 indicates a Series
        ChartName = "Chart " & Arg2 + 1 ' Arg1 is the series number
        With Worksheets("Sheet1")
            ' Hide all sub charts
            For i = 2 To .ChartObjects.Count
                .ChartObjects(i).Visible = False
            Next
            ' Show the required chart
            .ChartObjects(ChartName).Visible = True
        End With
    End If
End Sub

  3. Initialise the class module (best done as workbook open event: put this code in the ThisWorkbook module)

    Option Explicit

Dim myClassModule As New EventClassModule

Private Sub Workbook_Open()
    Set myClassModule.myChartClass = _
        Sheet1.ChartObjects("Chart 1").Chart
End Sub

Now, when a series on Chart 1 is selected, the related detail chart is shown, and the others are hidden

Useful links
MSDN Chart Object Events
MSDN Chart Select Event

Upvotes: 3

Related Questions