locked
Get the row and column values for the edge of a chart embedded in a worksheet RRS feed

  • Question

  • I am trying to use VBA to put 50 charts vertically in a worksheet. In a cell beside each chart I need to put a number there. The chart dimension is based on pixel, but the cell is based on row. It is hard to match between the two units (row vs pixel) and the output in cell always is sometimes mis-aligned with the corresponding chart.

    Is there a way that I can obtain the row number corresponding the top/bottom of the chart? If this information was available, I would be able to exactly align the cell output with the edge of chart. Thanks a lot for you help !!!
    Tuesday, September 25, 2012 3:27 PM

Answers

  • A ChartObject has properties TopLeftCell and BottomRightCell. These return the cells under the upper left and lower right corners of the chart, respectively. You can retrieve the Row property of these cells. For example:

        Dim TopRow As Long
        Dim BottomRow As Long
        With ActiveSheet.ChartObjects(1)
            TopRow = .TopLeftCell.Row
            BottomRow = .BottomRightCell.Row
        End With


    Regards, Hans Vogelaar

    Tuesday, September 25, 2012 3:46 PM

All replies

  • A ChartObject has properties TopLeftCell and BottomRightCell. These return the cells under the upper left and lower right corners of the chart, respectively. You can retrieve the Row property of these cells. For example:

        Dim TopRow As Long
        Dim BottomRow As Long
        With ActiveSheet.ChartObjects(1)
            TopRow = .TopLeftCell.Row
            BottomRow = .BottomRightCell.Row
        End With


    Regards, Hans Vogelaar

    Tuesday, September 25, 2012 3:46 PM
  • Big help...thank you very much, Has !!!
    Tuesday, September 25, 2012 4:00 PM
  • Dear LSDYNA,

    Pls try voting any post as helpful if it helps and mark as answer if it solves your issue.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, September 26, 2012 7:21 AM
    Answerer