CODEWITHSUNDEEP
vbaexcelgraph
DK23
DK23

Reputation: 29

Change chart data range using a drop down list (vba)

I'm trying to Change chart data range using a drop down list (vba). Here is my code:

Sub SelectTable()

With ActiveSheet.Shapes(Application.Caller).ControlFormat
    If ActiveSheet.Shapes(Application.Caller).Name = "Drop Down 2" Then
    Worksheets("Comparison").ChartObjects("Chart 8").Chart.SetSourceData Source:= _
    Range(.List(.Value) & "[#All]")
    Worksheets("Comparison").ChartObjects("Chart 8").Chart.PlotBy = xlRows

End If

End With

End Sub

Getting Run-time error: '1004'

while selecting the drop-down list.

Also, this VBA will not be based on any pivot table. It's going to make changes on a Dashboard sheet.

Yes, the chart has been created using Pivot but the drop down list is a combination of UserForm and Data Validation

Upvotes: 1

Views: 1002

Answers (1)

Eric K.
Eric K.

Reputation: 834

I assume that your listbox return a string of your named range

Sub SelectTable()

With ActiveSheet.Shapes(Application.Caller).ControlFormat
    If ActiveSheet.Shapes(Application.Caller).Name = "Drop Down 2" Then
    Worksheets("Comparison").ChartObjects("Chart 8").Chart.SetSourceData Source:= _
    worksheet("sheetName??").Range(.Value & "[#All]")

    Worksheets("Comparison").ChartObjects("Chart 8").Chart.PlotBy = xlRows

End If

End With

End Sub

Upvotes: 0

Related Questions