Return chart title, dimensions and location on worksheet

**learning_vba** · 05-16-2013, 02:15 PM

Hello,

I have a worksheet with many charts on it. For each chart on the page, I need to get the chart title, the dimensions (preferably in inches but pixels will work) and the location on the worksheet (probably relative to the top left corner of the worksheet). I need to return all these values to a blank worksheet. I have never worked with charts and VBA so I am not sure where to begin. All tips and suggestions are a much appreciated.

Thank you,

learning_vba

**BigBas** · 05-16-2013, 02:30 PM

Does this do the trick for you? (Change the variables for sheets names to suit)

Sub GetAllThatDataForTheMan()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim cht As ChartObject
    Dim chts As ChartObjects
    Dim nRow As Long
    
    ' Declare variable for sheet containing charts
    Set ws1 = Worksheets("Sheet1")
    ' Declare variable for sheet with resulting chart data
    Set ws2 = Worksheets("Sheet2")
    
    Set chts = ws1.ChartObjects
    
    For Each cht In chts
        nRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1

        ws2.Cells(nRow, 1) = cht.Name
        ws2.Cells(nRow, 2) = cht.Height
        ws2.Cells(nRow, 3) = cht.Width
        ws2.Cells(nRow, 4) = cht.Top
        ws2.Cells(nRow, 5) = cht.Left
    Next cht
End Sub