Thread: Change chart type by using data validation

Hi,

Am I at all close on this.

I have one chart and I want to cycle through 4 different types of charts using data validation.

I used the macro recorder to get some of the syntax and added it under the Case “Ratio” to test, but not getting anything to happen.

Can somebody help me out with the right way to go here?

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "C3" Then

    ActiveSheet.ChartObjects("Chart 1").Activate
    
    Select Case Range("C3").Value
        
        Case "Linear"
        
'        .Type = xlLinear
        
        Case "Power"
        
'        .Type = xlPower
        
        Case "Ratio"
            ActiveChart.ChartTitle.Text = "Ratio"
            ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
            ActiveChart.SeriesCollection(1).Trendlines(1).Select
            Selection.Type = xlLogarithmic
        
        Case "Parabola"
'            With Selection
'                .Type = xlPolynomial
'                .Order = 2
'            End With
        
    End Select
End If
End Sub

I confess I know little of working with Charts in VBA but I would point out that you need to use the Change event rather than SelectionChange event - ie you want the code to fire whenever C3 is altered.

So instead of:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "C3" Then
...
End If
End Sub

you would use

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target,Range("C3")) Is Nothing Then Exit Sub
...
End Sub

Other than that I can't really help much - it's not clear to me whether your trying to add and amend trend line types or the underlying chart type - it might help if you posted a sample file and perhaps provided examples of each chart that you wish to generate via the validation list.

Hopefully Andy and co. will be able to help you resolve.

(Probably worth just confirming that you are definitely using XL2007 - ie I suspect Charting options differ slightly between 2007 and predecessors)

You say chart type although the code would suggest you are changing Trendline type.

If it's trendline then you can do it all with formula and multiple data series.

Can you post example workbook.

Additional series on data sheet.

There are 4 new series in the chart. Each only displays a trendline.

If I've got it right, Andy created four new series, each of which is the exact same data, and each has a diffferent type of trendline. Selecting a type of trendline makes three of the series #N/A, making it and its trendline disappear, leaving the selected one remaining.

Andy is a very clever boy.

Each of the series has a trendline -- four series, four trendlines. Only one series and its trendline are displayed at any given time.

Shg's explanation is correct.

The only extra thing I did was to format the extra 4 series to have no data markers. The data markers are from your original series, which now has no trendline.