Selecting gridlines using VBA

  • Thread starter Thread starter Jon
  • Start date Start date
J

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
 
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
 
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/
_______
 
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
 
Back
Top