[Solved] Reduce code help

[BrianDP1977]

The following code is taken from a recorded macro that changes the displayed data on a chart after a button is clicked:

Private Sub OptionButton2_Click()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    
    ActiveChart.SeriesCollection(1).XValues = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
    ActiveChart.SeriesCollection(1).Values = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_Depart"
    ActiveChart.SeriesCollection(2).XValues = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
    ActiveChart.SeriesCollection(2).Values = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StrtS"
    ActiveChart.SeriesCollection(3).XValues = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
    ActiveChart.SeriesCollection(3).Values = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StpS"
    ActiveChart.SeriesCollection(4).XValues = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
    ActiveChart.SeriesCollection(4).Values = _
        "='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_CT"
End Sub

It works fine but I was wondering if there was a way to reformat it so the code was a little more succinct (i.e. get rid of the whole select thing and ActiveChart call and instead use a name ("Chart1") as the reference). If that's not possible, is there a way to at least make it so that the plot area on the chart isn't still selected after the macro runs?

 

[XLGibbs]

With ActiveSheet.ChartObjects("Chart 1")

For x = 1 to 4
.SeriesCollection(x).XValues = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
next x

.SeriesCollection(1).Values = _
"='Overview and Deployment Staff Slides(v22).xls'!Chart_T_DP6_Depart"
.SeriesCollection(2).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StrtS"
.SeriesCollection(3).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StpS"
.SeriesCollection(4).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_CT"

End With

 

[BrianDP1977]

For some reason I keep getting this error:

Run-time error '438':

Object doesn't support this property or method.

It happens at the

.SeriesCollection(x).XValues = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_DP6_NL"

line of code and is the same error I got when I tried to reduce the code a little different way.

 

[XLGibbs]

With ActiveSheet.ChartObjects("Chart 1") .PlotArea

Try that...should work though...hmmm maybe I am too tired to think clearly tonight.

 

[BrianDP1977]

Same error only now it occurs with that line. I have no idea what's going on since this method of code reduction has worked with other types of operations.

 

[XLGibbs]

Try this instead. I have had similar success with charts I have done the same type of operations.

With ActiveSheet.ChartObjects("Chart 1")
  .PlotArea.Select
 
With Selection

For x = 1 to 4
.SeriesCollection(x).XValues = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_NL"
next x

.SeriesCollection(1).Values = _
"='Overview and Deployment Staff Slides(v22).xls'!Chart_T_DP6_Depart"
.SeriesCollection(2).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StrtS"
.SeriesCollection(3).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_StpS"
.SeriesCollection(4).Values = _
"='Overview and Deployment Staff Slides (v22).xls'!Chart_T_DP6_CT"
End With
End With

 

[BrianDP1977]

It's still giving me the same error. I checked to see that everything was pasted in correctly and I'm pretty sure it is. I may just have to stick with the original code which would be fine.

All I need to know is how to make the sub not have anything selected after it's comleted running.

 

[BrianDP1977]

I'm just going to use the orginal code. I added a

ActiveWindow.Visible = False

line of code which seems to unselect the plot area. Everything works fine this way. Thanks for the hlep though.