Good Day All -
I have written the following code to update the data series within a chart based on the selectiona user makes in a drop down box. At times tand issue will occur where the series gets delted and an error will occur when I go to set the series values as the series has been deleted, thus the values cant be set. I have attempted to add error handling that would create a new series if this issue occurs, however when the error occurs the code ignores the error handler. Below is the code that I am using. Any feedback on how to address would be greatly appreciated. Thanks.
I have written the following code to update the data series within a chart based on the selectiona user makes in a drop down box. At times tand issue will occur where the series gets delted and an error will occur when I go to set the series values as the series has been deleted, thus the values cant be set. I have attempted to add error handling that would create a new series if this issue occurs, however when the error occurs the code ignores the error handler. Below is the code that I am using. Any feedback on how to address would be greatly appreciated. Thanks.
Code:
Sub FilterRegions()
Dim chtGood As String
Dim chtError As String
Dim chtLabels As String
With ActiveSheet.Shapes("Drop Down 1") 'amend name to whatever is correct
chtGood = Sheets("Summary").Range("C83").Text
chtError = Sheets("Summary").Range("C84").Text
chtLabels = Sheets("Summary").Range("C85").Text
ActiveSheet.Range("B38").Value = .ControlFormat.List(.ControlFormat.ListIndex)
ActiveSheet.Range("$A$41:$T$80").AutoFilter Field:=1, Criteria1:=ActiveSheet.Range("B38").Text
ActiveSheet.ChartObjects("Chart 15").Activate
On Error GoTo ErrorTrap:
ErrorTrap:
If Err.Number <> 0 Then
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
Resume
End If
ActiveChart.SeriesCollection(1).Values = chtGood
ActiveChart.SeriesCollection(1).XValues = chtLabels
ActiveChart.SeriesCollection(1).Name = "Good Response Stores"
With ActiveChart.SeriesCollection(1).Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(2).Values = chtError
ActiveChart.SeriesCollection(2).XValues = chtLabels
ActiveChart.SeriesCollection(2).Name = "Incomplete or No Response Stores"
With ActiveChart.SeriesCollection(2).Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
.Solid
End With
End With
End Sub