Selecting gridlines using VBA

[Jon]

Hi, I'm writing a Macro to smarten up some graphs. I get a problem
when trying to format the style of the gridlines. I get the error
"Select method of gridlines class failed". The code below works fine
on graphs with only a primary axis, but doesn't when you have primary
and secondary axes. What am I doing wrong?

ActiveChart.Axes(xlCategory).MajorGridlines.Select

With Selection.Border
.ColorIndex = 16
.Weight = xlHairline
.LineStyle = xlDot
End With

 

[Andy Pope]

Hi Jon,

You can test whether the gridlines are being used first.
You can also specify primary or secondary axis.

If ActiveChart.Axes(xlCategory, xlPrimary).HasMajorGridlines Then
With ActiveChart.Axes(xlCategory, xlPrimary).MajorGridlines.Border
.ColorIndex = 16
.Weight = xlHairline
.LineStyle = xlDot
End With
End If
If ActiveChart.Axes(xlValue, xlSecondary).HasMinorGridlines Then
With ActiveChart.Axes(xlValue, xlSecondary).MinorGridlines.Border
.ColorIndex = 16
.Weight = xlHairline
.LineStyle = xlDot
End With
End If

Cheers

 

[Jon Peltier]

Andy -

Only one problem - only the primary axes can have gridlines.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Andy Pope]

Doh! of course you are right, good catch.

Thankfully the IF() THEN code works when testing the secondary axis
gridlines as it reports FALSE. It would only error once you tried to
format them.

Cheers
Andy