VBA Chart Formatting - Plot Area Border / Horizontal gridelines

[MacGyver7640]

A bit sad I haven't been able to figure this out, but here goes.

I really appreciate your help because I'm stumped, can't think of any other variants to google!

1) This code does not appear to work as intended, it sets the width to 1.0 points. If I set it equal to 3, it sets it to 2 points (which also achieved with xlMedium. Given how much I've looked, I fear that this isn't possible. But that seems crazy! Why would it recognize numbers at all?

Activechart.PlotArea.Border.Weight = 1.5

2) I'm having trouble formatting my horizontal gridlines. For some reason this code changes the format to Long Dash Dot How might I change it to the regular dash?

ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Border.LineStyle = xlDash

Thanks!

 

[Jon Peltier]

If you're using Excel 2010 or 2013, use the macro recorder to sniff out the syntax you need. If you're using 2007, you're out of luck.

1) .Border.Weight is old-fashioned syntax, "deprecated" but still kind of works, and takes the arguments xlHairline, xlThin, xlMedium, and xlThick (the constant values are 1, 2, -4138, and 4). The constants only enumerate which value is selected, and does not provide the actual value. In Classic Excel (2003 and earlier), the actual line widths were (about) 0.25, 0.75, 1.5, and 2.25 points, but in New Excel (2007+) the line widths are (about) 0.25, 1, 2, and 3 points. When you specified Weight = 1.5, it probably took 2, applied xlThin, and left you with 1 point thickness.

Instead use .Format.Line.Weight and use the actual width you want:

ActiveChart.PlotArea.Format.Line.Weight = 1.5

2) Again, use the new style format:

ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Format.Line.DashStyle = msoLineLongDash

 

[MacGyver7640]

If you're using Excel 2010 or 2013, use the macro recorder to sniff out the syntax you need. If you're using 2007, you're out of luck.

1) .Border.Weight is old-fashioned syntax, "deprecated" but still kind of works, and takes the arguments xlHairline, xlThin, xlMedium, and xlThick (the constant values are 1, 2, -4138, and 4). The constants only enumerate which value is selected, and does not provide the actual value. In Classic Excel (2003 and earlier), the actual line widths were (about) 0.25, 0.75, 1.5, and 2.25 points, but in New Excel (2007+) the line widths are (about) 0.25, 1, 2, and 3 points. When you specified Weight = 1.5, it probably took 2, applied xlThin, and left you with 1 point thickness.

Instead use .Format.Line.Weight and use the actual width you want:

ActiveChart.PlotArea.Format.Line.Weight = 1.5

Jon, first thank you for all of the resources you provide. I recently used your clustered and stacked bar chart approach. It fit the bill just right!

Back to the question. This is perfect, thank you! That was the exact code I needed.

2) Again, use the new style format:

ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Format.Line.DashStyle = msoLineLongDash

Excellent, I had been trying the msoLine format as well and I didn't have the syntax (.format.line...) quite right. This is it, thanks! I will be using "msoLineDash" because I need the style with the smaller dashes.

As a side note, when you try to ask Excel what the line style is via the immediate window:

print ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Border.LineStyle

It prints out "-4115." Oddly enough, it prints out the same result whether you have the Dash style or the Long Dash style and trying to sue this in the Weight only gets you Long Dash. VBA for Excel 2007 must not be able to quite distinguish these two.

Again, thank you! Upgrading to Excel 2013 soon!

 

[Jon Peltier]

The older .Border.LineStyle syntax didn't know about so many styles of dashes, so it doesn't know how to reference them. What if you tried this:

? ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Format.Line.DashStyle

 

[MacGyver7640]

The older .Border.LineStyle syntax didn't know about so many styles of dashes, so it doesn't know how to reference them. What if you tried this:

? ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Format.Line.DashStyle

Sorry for being unclear! The sidenote was just a random note -- your first suggestions solved my problem completely. It was actually one of the last components of a toolbar I was building -- Thanks!