Position chart on a worksheet using row & column numbers in VBA.

[Darren Bartrup]

Hi all,

I've got a puzzle and I don't even know how to begin thinking it through.

The code below creates a chart object on the active sheet.

Sub CreateChart()
    Dim chtObject       As ChartObject
    
    Set chtObject = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=500, Top:=75, Height:=400)
End Sub

Is there a way to convert the Left and Top values (are they twips?) into row and column numbers, taking into account that rows and columns could be different heights and widths, so I can place the top left of the chart in, say, row 4 column 2?

And for a bonus puzzler - make it 4 column wide by 8 rows high? - or am I just taking the #*!$ now?

I've done so much work on this project, and this is the final step (creating the charts from the automatically gathered data).
Lots of brownie points, slaps on the back and resentful sneers coming my way if I get it to work

Thanks for your help in advance.
Darren.

 

[Darren Bartrup]

I might have found the answer (which always seem to happen after I post):

http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

Suppose I want to line up a chart to cover a range in the worksheet. I can do this very easily using a range object variable and chartobject variable:

Sub CoverRangeWithAChart()
         Dim RngToCover As Range
         Dim ChtOb As ChartObject
         Set RngToCover = ActiveSheet.Range("D5:J19")
         Set ChtOb = ActiveChart.Parent
         ChtOb.Height = RngToCover.Height ' resize
         ChtOb.Width = RngToCover.Width   ' resize
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
     End Sub

 

[MorganO]

Here is a quick adjustment to your code that will place your chart with the choosen cells. The top left of the chart will be in the center of the first cell and the bottom right of the chart will be in the center of the second cell.

Sub CreateChart(TopRow, TopColumn, BottomRow, BottomColumn As Integer)
    Dim chtObject       As ChartObject
 
    'Determine where to place the chart
    ChartLeft = Cells(TopRow, TopColumn).Left + Cells(TopRow, TopColumn).Width / 2
    Charttop = Cells(TopRow, TopColumn).Top + Cells(TopRow, TopColumn).Height / 2
    ChartWidth = Cells(BottomRow, BottomColumn).Left + Cells(BottomRow, BottomColumn).Width / 2 - ChartLeft
    ChartHeight = Cells(BottomRow, BottomColumn).Top + Cells(BottomRow, BottomColumn).Height / 2 - Charttop
 
 
    Set chtObject = ActiveSheet.ChartObjects.Add _
        (Left:=ChartLeft, Width:=ChartWidth, Top:=Charttop, Height:=ChartHeight)
End Sub
Sub test()
Call CreateChart(5, 5, 10, 10)
End Sub

Take care.

Owen

 

[Darren Bartrup]

Thanks for that Owen.
It's working perfectly, except if I put to start in cell B2 it starts in cell D2.
No doubt a simple correction needed, but I've got a date with a pint of beer and it can't be kept waiting.

I'll look into it tomorrow.

Thanks again,
Darren.

 

[MorganO]

Hmmm... very interesting. I've tested again on my end and if I tell it to start in Cell B2 (I.E. Call CreateChart(2, 2, 10, 10)) it creates the upperleft of the chart in Cell B2.

Owen

P.S. Hope the pint hits the spot!

 

[Darren Bartrup]

Thanks for the reply again Owen.

I figured out what's happening here.
The Cells command is using the active sheet, so if I run the code from a different sheet which has different cell widths it all goes pear shaped.

Easy!

P.S The pint hit exactly the right spot, so much so that I had to have another and then another and then I couldn't tell if it was hitting the spot anymore, so I tried one more time just to check (good error checking you see) and then my eyes stopped working and my vertical alignment fell out of sync and I ended up getting I.D.1.0.T errors - but got over them by using On Error Resume Next.

 

[MorganO]

P.S The pint hit exactly the right spot, so much so that I had to have another and then another and then I couldn't tell if it was hitting the spot anymore, so I tried one more time just to check (good error checking you see) and then my eyes stopped working and my vertical alignment fell out of sync and I ended up getting I.D.1.0.T errors - but got over them by using On Error Resume Next.

Lol! You know you have overindulged when you start thinking in VBA!