Scaling a chart that has been dynamically created in vb

I've got a VB app that creates charts based on data selections made by the
user. over time, the stuff in the DB will grow, so there's no easy way to
predict what the size of the chart should be. When it's built, it gets dumped
into a worksheet set aside just for that chart, along with some cells in
which various accumulations (monthly capcity, etc) get posted.

At the moment, the default size of the chart is 'small', (top left at cell
f13, bot-rt at cell p38). I'd like the chart to go from a1 to p38 (or
thereabouts), regardless of the number of data elements used to create it.

I have a book (Excel 2003 VBA Prog ref) that describes the excel obj model.
it implies there are some interesting methods that might be used to set TL
location and height & width -- but it's short on code examples on *how* to do
this.

Any pointers would be very welcome.

Thanks,
Mark (vb newbie)

Hi,

This is VBA code which runs within xl so you may need to tweak the
declaration of objCht and the use of Range to suit your vb app.

Sub SizeChart()
Dim objCht As ChartObject
Set objCht = ActiveSheet.ChartObjects(1)
With Range("A1:P38")
objCht.Left = .Left
objCht.Top = .Top
objCht.Width = .Width
objCht.Height = .Height
End With
End Sub

Cheers
Andy

Mark wrote:
> I've got a VB app that creates charts based on data selections made by the
> user. over time, the stuff in the DB will grow, so there's no easy way to
> predict what the size of the chart should be. When it's built, it gets dumped
> into a worksheet set aside just for that chart, along with some cells in
> which various accumulations (monthly capcity, etc) get posted.
>
> At the moment, the default size of the chart is 'small', (top left at cell
> f13, bot-rt at cell p38). I'd like the chart to go from a1 to p38 (or
> thereabouts), regardless of the number of data elements used to create it.
>
> I have a book (Excel 2003 VBA Prog ref) that describes the excel obj model.
> it implies there are some interesting methods that might be used to set TL
> location and height & width -- but it's short on code examples on *how* to do
> this.
>
> Any pointers would be very welcome.
>
> Thanks,
> Mark (vb newbie)

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy --- THanks for the code segment -- it works -- but it scales the
(apparently remembered) first ever created chart (chart 1). The current
number (monotonically increasinG is ine the 40's or 50's.

SO I guess the next question is either

a) How do I programmatically determine the number of the current chart?, or
b) How do I erase the app's knowledge of prior chart definitions and/or set
the current chart to a known (e.g. "1" ) value?

Or perhaps there's a c) I'm not VB-literate enough to think of.

Thanks again for your help.

Mark

"Andy Pope" wrote:

> Hi,
>
> This is VBA code which runs within xl so you may need to tweak the
> declaration of objCht and the use of Range to suit your vb app.
>
> Sub SizeChart()
> Dim objCht As ChartObject
> Set objCht = ActiveSheet.ChartObjects(1)
> With Range("A1:P38")
> objCht.Left = .Left
> objCht.Top = .Top
> objCht.Width = .Width
> objCht.Height = .Height
> End With
> End Sub
>
> Cheers
> Andy
>
> Mark wrote:
> > I've got a VB app that creates charts based on data selections made by the
> > user. over time, the stuff in the DB will grow, so there's no easy way to
> > predict what the size of the chart should be. When it's built, it gets dumped
> > into a worksheet set aside just for that chart, along with some cells in
> > which various accumulations (monthly capcity, etc) get posted.
> >
> > At the moment, the default size of the chart is 'small', (top left at cell
> > f13, bot-rt at cell p38). I'd like the chart to go from a1 to p38 (or
> > thereabouts), regardless of the number of data elements used to create it.
> >
> > I have a book (Excel 2003 VBA Prog ref) that describes the excel obj model.
> > it implies there are some interesting methods that might be used to set TL
> > location and height & width -- but it's short on code examples on *how* to do
> > this.
> >
> > Any pointers would be very welcome.
> >
> > Thanks,
> > Mark (vb newbie)
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>

You could use this to always deal with the last chart object.

Set objCht = ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.count)

Mark wrote:
> Hi Andy --- THanks for the code segment -- it works -- but it scales the
> (apparently remembered) first ever created chart (chart 1). The current
> number (monotonically increasinG is ine the 40's or 50's.
>
> SO I guess the next question is either
>
> a) How do I programmatically determine the number of the current chart?, or
> b) How do I erase the app's knowledge of prior chart definitions and/or set
> the current chart to a known (e.g. "1" ) value?
>
> Or perhaps there's a c) I'm not VB-literate enough to think of.
>
> Thanks again for your help.
>
> Mark
>
> "Andy Pope" wrote:
>
>
>>Hi,
>>
>>This is VBA code which runs within xl so you may need to tweak the
>>declaration of objCht and the use of Range to suit your vb app.
>>
>>Sub SizeChart()
>> Dim objCht As ChartObject
>> Set objCht = ActiveSheet.ChartObjects(1)
>> With Range("A1:P38")
>> objCht.Left = .Left
>> objCht.Top = .Top
>> objCht.Width = .Width
>> objCht.Height = .Height
>> End With
>>End Sub
>>
>>Cheers
>>Andy
>>
>>Mark wrote:
>>
>>>I've got a VB app that creates charts based on data selections made by the
>>>user. over time, the stuff in the DB will grow, so there's no easy way to
>>>predict what the size of the chart should be. When it's built, it gets dumped
>>>into a worksheet set aside just for that chart, along with some cells in
>>>which various accumulations (monthly capcity, etc) get posted.
>>>
>>>At the moment, the default size of the chart is 'small', (top left at cell
>>>f13, bot-rt at cell p38). I'd like the chart to go from a1 to p38 (or
>>>thereabouts), regardless of the number of data elements used to create it.
>>>
>>>I have a book (Excel 2003 VBA Prog ref) that describes the excel obj model.
>>>it implies there are some interesting methods that might be used to set TL
>>>location and height & width -- but it's short on code examples on *how* to do
>>>this.
>>>
>>>Any pointers would be very welcome.
>>>
>>>Thanks,
>>>Mark (vb newbie)
>>
>>--
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info