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/newsgroups...=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!

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"jdub765" <(E-Mail Removed)> wrote in message
news:E8AFBD78-54CB-4A8C-949B-(E-Mail Removed)...
> 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/newsgroups...=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!

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!


"Jon Peltier" wrote:

> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> http://PeltierTech.com
> _______
>
>
> "jdub765" <(E-Mail Removed)> wrote in message
> news:E8AFBD78-54CB-4A8C-949B-(E-Mail Removed)...
> > 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/newsgroups...=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!
>
>
>

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

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

With objChart.Application.ActiveCell

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"jdub765" <(E-Mail Removed)> wrote in message
news:08A2E9C1-AF92-4DE1-BA9A-(E-Mail Removed)...
> 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!
>
>
> "Jon Peltier" wrote:
>
>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "jdub765" <(E-Mail Removed)> wrote in message
>> news:E8AFBD78-54CB-4A8C-949B-(E-Mail Removed)...
>> > 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/newsgroups...=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!
>>
>>
>>

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/ChartsH...oveAChart.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"jdub765" <(E-Mail Removed)> wrote in message
news:A8B32B25-86DD-4988-B7EF-(E-Mail Removed)...
> 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
>