Chart Position in VB6

[jdub765]

I'm working in TestPartner, an automation tool that uses VB6, and I either
need to reposition a chart, or change the default position. I've been
searching to find a solution, but I can't seem to get it to work...

I found this link
http://msdn.microsoft.com/newsgroup...icedev-excel&lang=en&cr=US&sloc=en-us&m=1&p=1
and I think I need to use this code:

With ActiveSheet.Shapes("Chart 1")
.Left = ActiveCell.Left
.Top = ActiveCell.Top
End With

I don't know how to use the code above in my code below...

Private Sub GenerateGraph(objChart As Excel.Chart)
objChart.ChartType = xlPie
objChart.SetSourceData source:=objSheet.Range("B4:C5"), PlotBy _
:=xlColumns
objChart.location Where:=xlLocationAsObject, name:="Graph"
End Sub

Any help is greatly appreciated!

 

[Jon Peltier]

You need to change the position of the chart object, which is the shape that
contains the chart:

Private Sub GenerateGraph(objChart As Excel.Chart)
objChart.ChartType = xlPie
objChart.SetSourceData source:=objSheet.Range("B4:C5"), PlotBy _
:=xlColumns
objChart.location Where:=xlLocationAsObject, name:="Graph"
With ActiveCell ' or With Worksheets("Graph").Range("C12")
objChart.Parent.Left = .Left
objChart.Parent.Top = .Top
End With
End Sub

- Jon

 

[jdub765]

Thanks for the reply!
I tried the code you provided:
With ActiveCell
objChart.Parent.Left = .Left
objChart.Parent.Top = .Top
End With
and while there were no compiling errors, I get runtime errors.

When I use ActiveCell, I get Error 91 - Object variable or With block
variable not set, and when I use Worksheets("Graph").Range("C12"), I get
Error -2147221080 - Method Parent of object _Chart failed.
Both fail on objChart.Parent.Left = .Left.

I don't really understand how VB handles excel objects so I'm not sure how
to fix it. I've been guess & checking for about an hour. Do you have any
links that I can read so I can get a better understanding of what's going on?
All the searches I've done come up with links that don't really apply to my
problem...

Thanks a million for the help!

 

[jdub765]

omg. I found an easy solution.

I found the "Record Macro" function in Excel. lol
I clicked on "Relative Reference", moved the chart, and then looked at the
resulting code.
Then I changed the code a little to fit what I needed and I came up with this:

With ActiveSheet.Shapes("Chart 1")
.IncrementLeft -25.5
.IncrementTop 50.25
End With

And it works!! WOOHOO! Thanks for your help Jon Peltier! It helped me
understand what was going on a little more so that I could edit the code

 

[Jon Peltier]

You need to prefix ActiveCell with the object variable representing the
Excel application, or:

With objChart.Application.ActiveCell

- Jon

 

[Jon Peltier]

IncrementTop and IncrementLeft can have unexpected results, like if the
chart is created in a sheet with a different freeze panes configuration or a
window of a different size. Instead of these with relative distances, use
..Top and .Left with absolute references.

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

- Jon