Need macro to insert chart w/ FIXED size & location
 

Guys,

after I managed to select the dynamic data range for my chart I am now
inserting a chart via macro...

Sounds simple, but isnt for a newbie ;)

I recorded a macro to move and size the chart, to see which code it
uses:

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft 37.5
ActiveSheet.Shapes("Chart 3").IncrementTop 24#
ActiveSheet.Shapes("Chart 3").ScaleWidth 1, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 1, msoFalse,
msoScaleFromTopLeft


It looks like it moves the chart relative to its current position. but
I need to have the chart on FIXED points. It will be oriented along
cells (if that makes it easier) it will always be in the same spot and
although the amount of data varies the size of the chart must always be
the same...

How can i accomplish this? I was thinking there must be a command that
tells Excel the position of the corners or one corner and length and
width...

Any help aprreciated :)

Matt

Need macro to insert chart w/ FIXED size & location
 

Dim rng as Range
set rng = Range("B9").Resize(3,5)
With ActiveSheet.ChartObjects(1)
.Top = rng.Top
.Left = rng.Left
.Width = rng.Width
.Height = rng.Height
End With

--
Regards,


"Matt" wrote in message
oups.com...
Guys,

after I managed to select the dynamic data range for my chart I am now
inserting a chart via macro...

Sounds simple, but isnt for a newbie ;)

I recorded a macro to move and size the chart, to see which code it
uses:

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft 37.5
ActiveSheet.Shapes("Chart 3").IncrementTop 24#
ActiveSheet.Shapes("Chart 3").ScaleWidth 1, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 1, msoFalse,
msoScaleFromTopLeft


It looks like it moves the chart relative to its current position. but
I need to have the chart on FIXED points. It will be oriented along
cells (if that makes it easier) it will always be in the same spot and
although the amount of data varies the size of the chart must always be
the same...

How can i accomplish this? I was thinking there must be a command that
tells Excel the position of the corners or one corner and length and
width...

Any help aprreciated :)

Matt

Need macro to insert chart w/ FIXED size & location
 

Thanks Tom ! Havent had a chance to test it but it looks like just what
i need :)