Thread: vba to create chart in certain position after data is entered

Hi, I have a worksheet that I continually add data to. Each time I add a new row of data, I need a new chart to be generated under the previous chart that I added for the previous row's data. Obviously, the new chart will be in different location each time.

First, how is location specified in VBA when a new chart is created?

And secondly, how would code be written to locate the previous chart and place a new chart underneath it for each new row of data added?

Any suggestions would be greatly appreciated.

Thanks,
Buck

Hello Buck,

Please edit your post and amend your thread title to something descriptive of your problem. "Special chart creation" isn't accurate. Perhaps something like "vba to create chart in certain position after data is entered".

If you are using code to add the chart you can specify it's size on insertion.

This will get the position and size of the most recently added chartobject

Code:

with activesheet.chartobjects(activesheet.chartobjects.count)
   debug.print .left,.top,.width,.height
end with

There are plenty of examples on the forum of inserting, sizing and populating a chart via code. There is also the macro recorder.

That snippet was just the information required to determine where the last one was placed.

If you post an example workbook and explain how the code knows when to add a chart I'm sure we can help progress your problem.

If you post an example workbook and explain how the code knows when to add a chart I'm sure we can help progress your problem.

I think I have the design of the code figured out. What would be nice is if there was a way to count the number of charts on a page, and store that value.

Is that possible?

Also is it possible to define the chart's location with a variable rather than a value in this code?

Code:

Set myChtObj = Worksheets("Individual Jobs").ChartObjects.Add _
                    (Left:=12, Width:=370, Top:=2042, Height:=239)

Thanks for your time

there is a way to count the charts, I posted it before.

Code:

activesheet.chartobjects.count

Try building on this

Code:

Sub AddChart()
    
    Dim objLast As ChartObject
    Dim objNew As ChartObject
    
    If ActiveSheet.ChartObjects.Count = 0 Then
        Set objNew = ActiveSheet.ChartObjects.Add(10, _
                                                  10, _
                                                  400, _
                                                  300)
    Else
        Set objLast = ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count)
        Set objNew = ActiveSheet.ChartObjects.Add(objLast.Left, _
                                                   objLast.Top + objLast.Height, _
                                                   objLast.Width, _
                                                   objLast.Height)
    End If
                                                   
End Sub

Code:

 Worksheets("Sheet1").ChartObjects.Count

because for some reason your are trying to set a reference to the Number of chart objects.

If you use the WITH statement you need only type the sheet reference once.

Code:

with Worksheets("Graph Reference")
    Set objLast = .ChartObjects(.ChartObjects.Count)
end with