Chart VBA: How to bring series to foreground?

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
Hi, I have 38 years (columns) of temperature data, plotted by 2-week intervals (rows). I want to display 38 scatterplot charts--one for each year--let's call this the "study year". On each year's chart I wish to plot in grey all of the points for all of the years, except for the study year, which will be in red, in the foreground (not covered by other point). I have written a subroutine that will do this, except that the study year's points are not in the foreground--some are hidden behind other points (except for the 28th year's plot). Can anyone think of a way to have the points in the foreground? I can do this using the menus byt moving the study year's series to the bottom of the series list, but I don't know how to do this in VBA. The code I am using is below. Thank you!

Sub AddChartObject()
Dim j As Integer
Dim i As Integer

For j = 1 To 38
Dim myChtObj As ChartObject

Set myChtObj = Sheets("Charts1").ChartObjects.Add _
(Left:=j * 255, Width:=250, Top:=10, Height:=125)
myChtObj.Chart.SetSourceData Source:=Sheets("crosstabs weekly means by weekn").Range("e2:aq29")
myChtObj.Chart.ChartType = xlXYScatterLines
myChtObj.Chart.PlotBy = xlColumns
myChtObj.Chart.Legend.Font.Size = 4
myChtObj.Chart.Axes(xlValue).TickLabels.Font.Size = 6
myChtObj.Chart.Axes(xlCategory).TickLabels.Font.Size = 6

For i = 1 To 38
myChtObj.Chart.SeriesCollection(i).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(128, 128, 128)
Selection.MarkerBackgroundColor = RGB(128, 128, 128)
Selection.Border.Color = RGB(128, 128, 128)
Selection.Format.Line.Visible = False
Next i

myChtObj.Chart.SeriesCollection(j).Select
Selection.MarkerStyle = 2
Selection.MarkerSize = 5
Selection.MarkerForegroundColor = RGB(255, 0, 0)
Selection.MarkerBackgroundColor = RGB(255, 0, 0)
Selection.Border.Color = RGB(255, 0, 0)
Selection.Format.Line.Visible = False

Next j

End Sub
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

hmmm...

Board Regular
Joined
Feb 24, 2003
Messages
104
I figured out a way--I'm putting in a secondary axis for the "study year". Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

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