Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Thanked 0 Times in 0 Posts

    Chart Formatting (XP)

    Hi All,
    Trying to format a chart that is generated by another program which calls on Excel. Can format the chart on the fly via VBA. The problem is that depending on the data in the chart, sometimes the legend covers the data labels. I'd like to write an if/then statement that examines the .top value of the legend and datalabels, makes sure there is sufficient room between them and if not moves the datalabels incrimentaly.

    This is more complex than what I'm used to. Any sugestions?


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Chart Formatting (XP)

    This is NOT a trivial task.
    While the legend size can be determined "directly" using the top, left, height, and width properties, datalabels have only a top and left property they have no property for height and width (that I can find) for the height you would have to get the font.size and the font.fontstyle property and estimate how tall the datalabel is and then based on the text propery estimate how wide EACH of the letters of the string is and get the width of the datalabel "box"

    Even once you do this the datalabel are properties of the indiv points you will have to loop through ALL the series in the chart, and in each series loop through all the points check if it has a datalabel if is does, get the top and left properties, then from the other items estimate the width and height. The best bet is to feed it into an array of values. after filling the array, you must look at every point in the array and see if it has overlap with ANY other point in the array. If there is overlap one must be moved. If you move it you must make sure you don't move it to overlap a cell you already moved or that had no overlap before.

    The logic to get all the "areas" for each datalabel (as I mentioned) is not straightforward, but I think is "do-able" on some level.

    The algoritm to MOVE I can't even begin to decipher to make sure that none overlap.

    Hope this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts