[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?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,082
Messages
5,570,095
Members
412,311
Latest member
Mozz
Top