Excel VBA - Setting xlvalues axis color to graduate

Question

I'm probably having a bug in my Macro-Recorder since when I try to record a change of color in the xlvalues axis, that is not recorded. What I need to do is to set a gradient radial color with 2 different shades of grey and different transparency.

I'm using this code to set my chart up:

ActiveSheet.Shapes.AddChart.Select

    With ActiveChart
        .ChartType = xlRadar
        .SetSourceData Source:=Range(StartCell, StopCell) ' Those contain a range of interesting data
        With .SeriesCollection(1)
            .Name = "=""Line 1"""
            .Format.Line.Weight = 1.5
        End With
        With .SeriesCollection(2)
            .Name = "=""Line 2"""
            .Format.Line.Weight = 1.5
            .Format.Line.DashStyle = msoLineDash
        End With
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = Left(Cells(StartCell.Row, 1).Text, 2) & "." & Mid(Cells(StartCell.Row, 1).Text, 3, 1) & " GHz"
        With .Axes(xlValue)
            .TickLabels.NumberFormat = "# ""dB"""
            .Border.ColorIndex = 3 ' <- This is yet to be modified
        End With
    End With

How do I do that? I thought all that could be made in excel, could also be programmatically done in vba...

Nope. The .Border.ColorIndex works just fine changing the color... Only I have to set it to a grey gradient and not to red ( = 3) — Noldor130884
That would be ok if I wanted to set a normal color... But I really need a gradient like this: elliottback.com/wp/wp-content/excel-gradient-graph.png — Noldor130884
The link you provided shows the background of the chart filled with a grey gradient. is that what you want to do? — user3616725

David Zemens David Zemens · Accepted Answer · 2014-07-23T16:16:18

I think you are not going to be happy with the answer I have but here goes.

This appears to be a bug that MS never fixed.

If you go to the object browser or Locals window there is literally nothing about the axes' Format.Line properties that lets you manipulate gradient. Observe while recording a macro, and no matter what manual action I take on that dialog, the recorder only records the .Visible = msoTrue.

Here is what I would like to get as a result:

enter image description here

While you can manipulate the axis' .Format.Fill gradient, that appears visually only to affect the axis value labels, and not the axis line(s) themselves.

enter image description here

I observe the same limitations with line Shapes, which sometimes can be used very similarly to the axis lines. No dice, it is the same problem: You can do it manually, but the recorder yields nothing, and the object browser shows no relevant properties.

MS's documentation about Axis.Format is even worse!

enter image description here

This appears to be a problem that has existed for a long time

Here is Jon Peltier lamenting this problem over 7 years ago. The gradient fill as it pertains to many shapes was new in 2007. It appears that they never really made it robust in that release, nor have they fixed it since.

I just looked through the Object Browser and the online help for 2007. There are a few new things listed, like LinearGradient and ColorStops, which are new in 2007. However, none of these are associated with Line or LineFormat, and Line and LineFormat have no new properties or methods. The VBA Language Reference in MSDN does not yet include Office 2007. I haven't found any explanation anywhere of how to program to the new shapes. The lack of macro recorder functionality to provide insights into this new object model is very unfortunate.

And another Microsoft MVP describing this as essentially hopeless, as far as VBA is concerned. This is for PowerPoint, but the general functionality and methods should be the same: a Shape in PPT is not really different than a Shape in XLS or Word. Likewise a Chart in any application is now all the same Excel charts, not MSGraph.

It does seem to be a bug but it does not appear to have a solution:

The properties you might try to access (e.g., .Format.Line.Fill..., etc.) simply do not exist -- well, actually I suspect they do exist, but they have not been exposed to you or to VBA. Attempting to call them will raise an error :(

Excel VBA - Setting xlvalues axis color to graduate

1 Answers