CODEWITHSUNDEEP
vbaexcel
Tollbooth
Tollbooth

Reputation: 86

Dynamic Excel graph

At the moment, I have created four different graphs that appear only if they are called in my drop-down box on cell D5. However, I am trying to create a single dynamic graph that populates its data depending on what is in cell D5.

Is this possible?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    ' The variable KeyCells contains the cells that will cause an alert when they are changed.
    Set KeyCells = Range("D5")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

        If Range("D5") = "Tremont" Then
            ActiveSheet.ChartObjects("Tremont").Visible = True
            ActiveSheet.ChartObjects("SaybrookPointe").Visible = False
            ActiveSheet.ChartObjects("21Fitzsimons").Visible = False
            ActiveSheet.ChartObjects("Mezzo").Visible = False

        ElseIf Range("D5") = "Saybrook Pointe" Then
            ActiveSheet.ChartObjects("Tremont").Visible = False
            ActiveSheet.ChartObjects("SaybrookPointe").Visible = True
            ActiveSheet.ChartObjects("21Fitzsimons").Visible = False
            ActiveSheet.ChartObjects("Mezzo").Visible = False

        ElseIf Range("D5") = "21 Fitzsimons" Then
            ActiveSheet.ChartObjects("Tremont").Visible = False
            ActiveSheet.ChartObjects("SaybrookPointe").Visible = False
            ActiveSheet.ChartObjects("21Fitzsimons").Visible = True
            ActiveSheet.ChartObjects("Mezzo").Visible = False

        ElseIf Range("D5") = "Mezzo" Then
            ActiveSheet.ChartObjects("Tremont").Visible = False
            ActiveSheet.ChartObjects("SaybrookPointe").Visible = False
            ActiveSheet.ChartObjects("21Fitzsimons").Visible = False
            ActiveSheet.ChartObjects("Mezzo").Visible = True

        End If

    End If

End Sub

Upvotes: 0

Views: 139

Answers (2)

Kanike Vamshi Krishna
Kanike Vamshi Krishna

Reputation: 327

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
' The variable KeyCells contains the cells that will cause an alert when they are changed.
Set KeyCells = Range("D5")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

If Range("D5") = "Tremont" Then
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).XValues = Range(X_axis_values)
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Name = "Tremont"
    Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Values  = Range(Y_axis_values)


'If a bar graph,
with Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB= RGB(0,0,0)
.Transparency = 0
.Solid
End With          

 ElseIf Range("D5") = "Saybrook Pointe" Then
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).XValues = Range(X_axis_values)
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Name = "Saybrook Pointe"
     Activesheet.Chartobjects("Single_Dynamic_Chart").FullSeriesCollection(1).Values  = Range(Y_axis_values)

'If a bar graph,
with Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB= RGB(0,0,0)
.Transparency = 0
.Solid
End With          

        ElseIf Range("D5") = "21 Fitzsimons" Then
            'Similarly like above cases, define the X-axis,the series name and the values.
        ElseIf Range("D5") = "Mezzo" Then
            'Similarly like above cases, define the X-axis,the series name and the values.
        End If

    End If

End Sub

Following these links would give you more information on how to work more on charts

Intoduction to charts

Chart series elements

Upvotes: 1

A.S.H
A.S.H

Reputation: 29332

I see no problem with this. But you can make it simpler and avoid hardcoding the names, and you can save some inc as well:

Dim ch As ChartObject
For Each ch in ActiveSheet.ChartObjects
    ch.Visible = ch.Name = Range("D5").Value
Next

But, well, you need to remove the spaces from the names in the D5 list, letting them be exactly equal to the charts' names.

Upvotes: 1

Related Questions