Multi choice data source select for a chart
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.
Code:
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