Detect Text Box covering Chart Element

[pbornemeier]

I have some code that adds a text box to a column chart at a fixed location from the top and left of the chart. Currently, if the text box covers the top end of any column, I manually reposition it so that is either in an area at the top of the chart where no bars extend, or to the lower half of the chart where all of the bars would go past the top of the text box.

Is there any way to detect whether a text box (or any shape for that matter) overlaps one or more chart elements (i.e. column, legend, title) or for that matter another shape or text box?

Is there a way to determine the location of a "clear area" (of specified size) where a text box could be placed?

I'm sure there could be some cases where the end of a bar must be covered, but if that happens, I will have to live with it.

 

[pbornemeier]

It looks like the Excel4 Macro GET.CHART.ITEM could be used to accomplish this.

GET.CHART.ITEM(x_y_index, point_index, item_text)

• <TT>x_y_index</TT> is 1 to return the x position and 2 to return the y position.
• <TT>point_index</TT> depends on the item we're looking at, but is a number from 1 to 8 to identify a specific vertex within the item. For example, 2 is the upper middle of any rectangular item, such as a column in a column chart.
• <TT>item_text</TT> identifies the item we're interested in, such as "Plot" for the plot area, or "S2P4" for the fourth data point in the second series in the chart. If item_text is omitted then the selected item information is returned.

Partial documentation here: http://www.informit.com/articles/article.aspx?p=374498&seqNum=2
I will keep looking for a full list of the valid values for the 3rd parameter (Known valid items include: Plot, Legend, Chart, S2P4 (Series 2, Point 4)