Dynamic Chart Series Values

[N8theGreat]

I have 5 sets of data I'd like to be able to display using only one chart that is controlled by a listbox. I've tried a couple of different lines of code and searched to find an appropriate answer for this without any luck. Anyone willing to help?

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$D$1"
    If Cells("$D$1") = "Values1" Then
        ActiveSheet.ChartObjects("Chart 1").Chart.Activate
            ActiveChart.SeriesCollection(1).XValues = Range(Cells(43, 1), Cells(1284, 1))
            ActiveChart.SeriesCollection(1).Values = Range(Cells(43, 4), Cells(1284, 4))
    End If
    If Cells("$D$1") = "Values2" Then
        ActiveSheet.ChartObjects("Chart 1").Chart.Activate
            ActiveChart.SeriesCollection(1).XValues = Range(Cells(43, 13), Cells(1283, 13))
            ActiveChart.SeriesCollection(1).Values = Range(Cells(43, 16), Cells(1283, 16))
    End If
Case Else
End Select
End Sub

I could write this code in a module and have it triggered, but I'd like the changes to occur upon input.

 

[NdNoviceHlp]

Avoid the ws change code. Put the code in sheet code as a sub then use your listbox selection as input to call the sub. HTH. Dave

 

[N8theGreat]

Thank you. I don't know the syntax for calling the data in the listbox. Would if be something like ...

with Cells(X,XX)
     If Cells(x,XX) = value1 then
         use a set of data in the chart
     else
     If Cells(X,XX) = value2 then
         use a different set of data
     End if

I don't now what the syntax of that code is supposed to look like, or the best way to trigger those actions. If anyone has something close that I would be able to use as a template, I would be very appreciative. I'm pretty good at the triggered global macros, but I lack much of the skill in the private subs of the individual worksheets. Any help or direction on some training material would rewarded with some Central Waters Bourbon Barrel Stout, if so desired.

Nate

 

[N8theGreat]

In case anyone was hanging off the edge of their seat for an answer to this problem, this is the solution I used.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$D$1"
    If Cells(1, 4) = "1Value" Then  'The data validation in D1 are named ranges 
        ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues =_
        Range(Cells(43, 1), Cells(1284, 1))
        ChartObjects("Chart 1").Chart.SeriesCollection(1).Values = _
        Range(Cells(43, 4), Cells(1284, 4))
    End If
    If Cells(1, 4) = "2Value" Then
        ChartObjects("Chart 1").Chart.SeriesCollection(1).XValues =_
        Range(Cells(43, 7), Cells(1270, 7))
        ChartObjects("Chart 1").Chart.SeriesCollection(1).Values =_
        Range(Cells(43, 10), Cells(1270, 10))
    End If
        'Etc... for all potential values 
    Case "$D$3"  'this changes the max value of the y-axis
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value + 10
    Case "$D$5"    'this changes the min value of the y-axis
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value - 10
       Case Else
End Select

I then used two data validation cells (one dependent validation using the indirect fomula) for an active output. The rest of the outputs I used nested If statements and vlookups to find the value from the dependent data validation. If anyone wants to see the code in the workbook, I could sanitize the data an headings and post the result I came up with.

If anyone knows of an easier way to actively change chart contents, I am open to learning new ways to execute.