A quick post today to respond to a user of my Quick XY Chart Utility. This noncommercial utility generates XY charts using input data in a variety of layouts, beyond X in first column and Y in subsequent columns.
The utility does not apply any particular formatting to the output charts, using the defaults it finds. One user of the utility had charts with over 100 series, and he wanted them all to be formatted identically.
In Excel 2003 and earlier, this is easy to do manually. Format the first series as desired, select the next series (use the up arrow key), press the F4 key, repeat the up arrow/F4 cycle until all series look the same. However, in Excel 2007, the F4 (Repeat Last Action) functionality has been eviscerated, particularly in charts. This is one of the productivity-sapping changes I have discussed in the past.
I wrote a little VBA procedure to apply the same formatting to all series in the chart. I wrote the routine in Excel 2003, but it will work fine in Excel 2007. It does not make use of the more extensive formatting capabilities introduced in Excel 2007, and that is just as well, since they do nothing to enhance the effectiveness of a chart.
Sub FormatAllSeriesTheSame() Dim srs As Series For Each srs In ActiveChart.SeriesCollection srs.MarkerStyle = xlMarkerStyleNone With srs.Border .ColorIndex = 5 ' blue lines .Weight = xlThin .LineStyle = xlContinuous End With Next End Sub
Select the chart and run the code.
We can improve on this routine, making it a better experience for the user. First, the following routine can check that there is an active chart. If not, the code exits gracefully with a reminder that the user should select a chart first. Second, to avoid the screen flashing and to speed up the reformatting, the routine deactivates ScreenUpdating while it is reformatting the chart.
Sub FormatAllSeriesTheSame() Dim srs As Series If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", _ vbExclamation + vbOKOnly, "No Active Chart" Else Application.ScreenUpdating = False For Each srs In ActiveChart.SeriesCollection srs.MarkerStyle = xlMarkerStyleNone With srs.Border .ColorIndex = 5 ' blue .Weight = xlThin .LineStyle = xlContinuous End With Next Application.ScreenUpdating = True End If End Sub
If you aren’t sure how to use this code, please read my tutorial about How To Use Someone Else’s Macro.