Formatting Transparency of Axes in VBA

[jjjjjjj]

I'm sure this isn't news here, but Excel 2007's Record Macro function doesn't play nice with formatting charts.

What I need to do is adjust the transparency for my (xlCategory, xlSecondary) and (xlValue, xlPrimary) axes. I've tried many different options for <!--?--><!--?-->"?" within ActiveChart.Axes(xlCategory, xlSecondary)."?"<!--?-->.Transparency = 0.75 and ActiveChart.Axes(xlValue, xlPrimary)."?"<!--?-->.Transparency = 0.75 from extensive research, but they all seem to either be invalid or have no effect. I've been unable to find out how to implement VBA code that achieves the following functions from within Excel:

Chart Tools > Layout > Axes > Secondary Vertical Axis > More Secondary Vertical Axis Options > Line Color > Solid Line > Transparency > 75%
Chart Tools > Layout > Gridlines > Primary Horizontal Gridlines > More Primary Horizontal Gridlines Options > Line Color > Solid Line > Transparency > 75%

My VBA code for manipulating my chart seems to go back to the "Automatic" setting for Line Color within the Format options. Does this need to be set to "Solid line" somehow before setting the transparency?

I might also need to change the color of these axes once this problem is solved...

Thank you very much for your help. It's very much appreciated.

 

[pgc01]

Hi

This is an example for the Y axis:

Sub test()
Dim cht As Chart
Dim YAxis As Axis

Set cht = ActiveChart
Set YAxis = cht.Axes(xlValue)

With YAxis.Format.Line
    .Visible = msoTrue
    .ForeColor.RGB = vbBlue
    .Transparency = 0.75
End With

End Sub