[Solved] Reduce code help

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146
The following code is taken from a recorded macro that changes the displayed data on a chart after a button is clicked:

Code:
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?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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

Board Regular
Joined
Nov 5, 2005
Messages
146
For some reason I keep getting this error:

Run-time error '438':

Object doesn't support this property or method.

It happens at the

Code:
.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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
With ActiveSheet.ChartObjects("Chart 1") .PlotArea

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

BrianDP1977

Board Regular
Joined
Nov 5, 2005
Messages
146

ADVERTISEMENT

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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Try this instead. I have had similar success with charts I have done the same type of operations.

Code:
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

Board Regular
Joined
Nov 5, 2005
Messages
146
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

Board Regular
Joined
Nov 5, 2005
Messages
146
I'm just going to use the orginal code. I added a

Code:
ActiveWindow.Visible = False

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,995
Messages
5,834,779
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top