Add a shape to the XAxis in a chart

Hi,
I hope this is the right forum. Since I wanted to do this with VBA I put it here, even though it's a chart question.

Here goes.
I am trying to find out if it is possible to add a shape (rectangle) to cover the XAxis line in a scatter chart. The XAxis moves based on the min and max values of the YAxis so it is not always in the center (tried the half height trick).
I get it to the chart but have to position it manually every time.
Can I find the XAxis position relative to the chart height?

It may not be possible but I thought I would ask anyway.
Hopefully my question makes sense, it's hard to get what I am thinking on 'paper' sometimes

Thanks in advance for any help or suggestions.
I appreciate it.

D

I am not sure I understand exactly what you are trying to do. Perhaps a few observations/questions will help clarify:

Can I find the XAxis position relative to the chart height?

Yes, you can. You can use the .Top property to find the position of the axis relative to the chart area, or you can use the .CrossesAt property (combined with the .MaximumScale and/or .MinimumScale properties) to determine where the horizontal axis is positioned. It appears that your goal is to position the axis in the "center" (whether that is chart area center or center of vertical axis is unclear). The .Top property is read only, so it cannot be used to position the axis, but the .CrossesAt property is read/write, so you can use that property to move the axis.

If your goal is to "hide" the axis so it is not visible, simply format the axis so it is invisible (line color is white if your chart has a white background for example).

Help file for the Axis object with links to each property: https://docs.microsoft.com/en-us/off...excel.axis.top

Does any of that help?

My first thought is to use a dummy data point and its data label for this, rather than a "free" rectangle shape. Depending on exactly what is going on in this chart, VBA may not even be required.
1) Enter/calculate X,Y positions for the dummy point in the spreadsheet. The big question that I see is how does your chart choose where the X axis is positioned -- what setting checked in the "horizontal axis crosses at" property of the format vertical axis dialog.
2) Add this point to the chart as an additional data series and add a data label to this point. Enter the information into the data label or enter the information into a cell and use that cell as the data label text (feature natively supported in Excel 2013 and later, for 2010, you will either need to make your own VBA procedure that will link the text to the cell or use an add-in like Rob Bovey's free XY chart labeler add-in).

When it's all set up, the position of the dummy point will be at/near the x axis position, and the attached data label will follow it around.

The devil is in the details, so we would probably need some more detail about your sheet/chart to help with those details, but that's basically how I would expect to do this.

Your attachment failed to attach. Be sure to follow the instructions in the yellow banner at the top of the page.

Your "line shape" that you have placed over the x axis contains no text, but looks like a "thick" line (about 7 or 8 points?) rather than the default thin line used by the x axis. I move your shape out of the way (eventually to delete once I'm done), then enter the format axis dialog. In the "line style" section, I choose a suitable width (as noted, about 7 or 8 points or whatever tickles your fancy). In the line color section, choose the gradient line option, then fiddle with the various settings to get the appropriate green-yellow-red gradient look that you want. The main advantage to using axis formatting rather than a superimposed shape object is that the chart automatically knows where the axis belongs, and there is no need for a routine that will reposition the object with each change/recalculate of the chart.

As for the text boxes with the information above and below, again, I would prefer a data label attached to a dummy series rather than free floating text boxes. I enter 60 in two cells, 300 in an adjacent cell and -100 in another adjacent cell. Enter "information above" and "information below" in two more adjacent cells. Then add a new data series, then run my "add data labels" routine from Rob Bovey's add-in and tell it to use the text in C1 and C2 for the data label text positioned below the data point. Format the data series so they are invisible (no marker and no line). Again, the advantage of using the data label instead of a free floating text box is that the position of the data label is tied to the plotted data point. As the chart changes/recalculates, the data label text will follow its attached data point.

I didn't spend a lot of time on this, but look at the direction I'm going and see if you like it.

That's not the kind of VBA I do, so I am not sure how to change the line gradient formatting using VBA. Usually I try recording a macro while making the changes and use the recorded code, but, in this case, the macro recorder doesn't tell me anything. Let me see if anyone else knows how to access these formatting options in VBA.

In the meantime, it might help someone if you explain exactly what you need VBA to do. Does VBA need to build the entire chart from scratch or just make changes to the axis gradient coloring or some other scope?

I don't think that functionality is exposed in VBA unfortunately. There are quite a few gaps in the object model when it comes to charts.

You might be able to use copy/paste

If you select the chart and Copy. Then select a chart without the gradient axis line and do Paste > Paste Special > Formats.
It's possible that this may change something unintended, but that may be accessible via code so it can be reset.

Please Login or Register  to view this content.

Going in a different direction, another approach to adding the bar at the x axis might be to do a Scatter + (stacked?) bar combination chart where you have one or a few data points on the bar chart positioned at the x axis of the scatter chart to give the colored bar. In order to position the bar, you would probably use a date axis on bar's vertical axis and then have a routine that will read the max, min, crosses at properties of the scatter vertical axis to choose the vertical position of the bar. Is that too different from the current approach?