Multi choice data source select for a chart

[Aussiebear]

I am wanting to make a multi choice dropdown to select a data range for the Primary series in a chart, but the following returns an error 424 object required. What have I done wrong?

Data Sources are named tables (tblCharlevilletemps, tbleDalbyTemps, tblToowoombaTemps, tblWarwickTemps) and consist of three columns A:C on 4 sheets named (Charleville, Dalby, Toowoomba and Warwick), respectively. Similar to the other threads where a chemical was a multi select choice as the secondary axis.

Sub Create_Dynamic_Chart()
Dim sht As Worksheet
Dim chrt As Chart
Dim data_rng As Range
Set sht = ActiveSheet
Set data_rng = Range("A2").Value
Select Case dRange
    Case Range("A2").Value = "Charleville"
        data_rng = tlCharlevilleTemps
    Case Range("A2").Value = "Dalby"
        data_rng = tblDalbyTemps
    Case Range("A2").Value = "Toowoomba"
        data_rng = tblToowoombaTemps
    Case Range("A2").Value = "Warwick"
        data_rng = tblWarwickTemps
End Select
Set chrt = sht.Shapes.AddChart2(Style:=-1, Width:=900, Height:=200, Left:=Range("G2").Left, Top:=Range("G2").Top).Chart
With chrt
    .SetSourceData Source:=data_rng
    .ChartType = xlLineStacked
    .ChartTitle.Text = Range("A2").Value & " Min & Max Temps"
    .SetElement msoElementDataLabelOutSideEnd
    .SetElement msoElementPrimaryValueGridLinesMajor
    .SetElement msoElementLegendBottom
    .SetElement msoElementPrimaryCatergoryAxixTitleBelowValue
End With
End Sub

[jdelano]

There is a typo here, missing the b in tblCharleville:

data_rng = tlCharlevilleTemps

likely unrelated to your issue.

[georgiboy]

Good spot jdelano,

I have included that in the below. I got the below to work after having a play (not all of your setting for the chart are included and i set it to xlLine):

Sub Create_Dynamic_Chart()
    Dim sht As Worksheet
    Dim chrt As Chart
    Dim data_rng As Range
    Dim dRange As Range
    
    Set sht = ActiveSheet
    Set dRange = Range("A2") ' dropdown
    
    Select Case dRange.Value
        Case "Charleville"
            Set data_rng = Range("tblCharlevilleTemps")
        Case "Dalby"
            Set data_rng = Range("tblDalbyTemps")
        Case "Toowoomba"
            Set data_rng = Range("tblToowoombaTemps")
        Case "Warwick"
            Set data_rng = Range("tblWarwickTemps")
    End Select
    
    Set chrt = sht.Shapes.AddChart2(Style:=-1, Width:=900, Height:=200, Left:=Range("G2").Left, Top:=Range("G2").Top).Chart
    
    With chrt
        .SetSourceData Source:=Range(data_rng.Address(, , , 1))
        .ChartType = xlLine
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = Range("A1").Value & " Min & Max Temps"
    End With
End Sub

You could also do the same with less code:

Sub Create_Dynamic_Chart2()
    Dim chrt As Chart
    
    Set chrt = ActiveSheet.Shapes.AddChart2(Style:=-1, Width:=900, Height:=200, Left:=Range("G2").Left, Top:=Range("G2").Top).Chart
    With chrt
        .SetSourceData Source:=Range(Range("tbl" & Range("A2").Value & "Temps").Address(, , , 1))
        .ChartType = xlLine
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = Range("A2").Value & " Min & Max Temps"
    End With
End Sub

[Aussiebear]

Yes you are correct, it is a typo but it still doesn't like the line

set data_rng =  Range("A2").Value

and.... its the simplest things that you cant see in the forest. Corrected the Range("A2").value

and now it says
the first line is incorrect as its undefined

Sub Create_Dynamic_Chart()
Dim sht As Worksheet
Dim chrt As Chart
Dim data_rng As Range
Set sht = Sheet1
Set data_rng = Worksheet("Chart").Range("A2").Value
Select Case dRange
    Case Range("A2").Value = "Charleville"
        data_rng = tblCharlevilleTemps
    Case Range("A2").Value = "Dalby"
        data_rng = tblDalbyTemps
    Case Range("A2").Value = "Toowoomba"
        data_rng = tblToowoombaTemps
    Case Range("A2").Value = "Warwick"
        data_rng = tblWarwickTemps
End Select
Set chrt = sht.Shapes.AddChart2(Style:=-1, Width:=900, Height:=200, Left:=Range("G2").Left, Top:=Range("G2").Top).Chart
With chrt
    .SetSourceData Source:=data_rng
    .ChartType = xlLineStacked
    .ChartTitle.Text = Range("A2").Value & " Min & Max Temps"
    .SetElement msoElementDataLabelOutSideEnd
    .SetElement msoElementPrimaryValueGridLinesMajor
    .SetElement msoElementLegendBottom
    .SetElement msoElementPrimaryCatergoryAxixTitleBelowValue
End With
End Sub

[georgiboy]

and now it says
the first line is incorrect as its undefined

That is because you have the variable defined as a range but you are using Set to assign a value, you would need to remove the '.Value'

See post 3 for another option

[Aussiebear]

Sorry Georgiboy, but your code allegedly fails at the

.SetSourceData Source:=Range(Range("tbl" & Range("A2").Value & "Temps").Address(, , , 1))

[georgiboy]

Have you checked the names of your tables, the typo may have been in your table name?

See the attached file I have been playing with.

[Aussiebear]

Stop the train! Something is happening with the workbook. Now I'm getting an empty chart.

[Aussiebear]

Have confirmed the table names are correct.

[georgiboy]

What happens with my attachment?

Can you share the file you are working on?

[Aussiebear]

In the morning, I shall post it.

[Aussiebear]

I'm in a state of confusion now..... so here it is.

[georgiboy]

For me it seems to work fine, however, I had to correct the typo:

In your dropdown list you have 'Charelville' and in your code you have 'Charleville'.

I also noted that the dates you have in the 'Dalby' tab don't really work and they are just integers.

Lastly I would recommend using tables for your data in the four tabs rather than named ranges, this way when you add new data, the table will expand and so will the range for the chart.

[Aussiebear]

Okay, I have converted named ranged to Tables, Corrected the typo for Charleville, and corrected the integers to proper dates.

Your code works correctly, only the date labels at the bottom are positioned at the Zero level yet the charts being created have a minimum level of -10. How can I fix that issue?

[georgiboy]

Here is another idea:

Rather than create a new chart each time, just change the data source of the chart that already exists on the sheet. In the below example the chart I was working with was named "Chart 4". This way you can format the chart and the changes will stick.

Sub Create_Dynamic_Chart2()
    Dim sht As Worksheet
    Dim chrt As ChartObject
    Dim data_rng As Range
    Dim dRange As Range
    
    Set sht = ActiveSheet
    Set dRange = Range("A2") ' dropdown
    
    Select Case dRange.Value
        Case "Charelville"
            Set data_rng = Range("tblCharlevilleTemps")
        Case "Dalby"
            Set data_rng = Range("tblDalbyTemps")
        Case "Toowoomba"
            Set data_rng = Range("tblToowoombaTemps")
        Case "Warwick"
            Set data_rng = Range("tblWarwickTemps")
    End Select
    
    Set chrt = sht.ChartObjects("Chart 4")
    With chrt.Chart
        .SetSourceData data_rng
        .ChartTitle.Text = Range("A2").Value & " Min & Max Temps"
    End With
End Sub

In answer to your question above:
Lower the labels on the Y axis: Right click on the date axis, select 'Format Axis', in the 'Labels' part, change the dropdown to the 'Low' option.

If you format the chart with the above and also use the code above, the changes on the chart will stick rather than resetting each time you add a new chart.

[georgiboy]

If you can share your updated file with the tables etc. I can have a look at the Chem type and period, if you wish?

[Aussiebear]

Okay.

[georgiboy]

I had started working on this before you sent your latest file so it comes without the additional sheets you added. I hope you can follow what I have done so that you can add the extra sheets yourself, if not then you can always shout. It maybe that someone has a better solution, anyhow, I hope the attached will help 'you' or others to help you get to the final product.

[Aussiebear]

Thank you georgiboy. Works very well. I will include the data for Roma, Goondiwindi, Kingaroy, & Gatton to build a database. I have also included a visual display below the chart to reflect SDS information, so it looks good from my perspective.

[snb]

My interpretation.
I also added the period selection.
It takes only 1 worksheet_change macro.