problems changing the size of my chart

[filo666]

Hi, I have this code in a userform (in the Ok button):

Private Sub CommandButton1_Click()
ActiveChart.PageSetup.ChartSize = xlScreenSize
ActiveChart.ChartArea.Height = CLng(TextBox1)
ActiveChart.ChartArea.Width = CLng(TextBox2)
End
End Sub

when I select the chart and run the macro the following error occurs:

Run-time error '1004':

Unable to set the height property of the ChartArea class.

I have no Idea why is this happening; I chacked everithing and it still not
runing

Any suggestion

TIA

 

[Jon Peltier]

You can only change the size and position of the ChartArea in a chart sheet,
not an embedded chart.

- Jon

 

[Peter T]

ChartArea dimensions are read only. If you are dealing with an embedded
chart

ActiveChart.Parent.Width = newWidth

Note chartobject size is a tad larger than ChartArea size allowing for
border.

If(?) your intention is to change the size prior to printing (or viewable
size of a ChartSheet), change the margins under ActiveChart.PageSetUp, eg
..LeftMargin

Probabably better first to validate your textboxes contain valid dimentions.

NEVER use the End statement

Regards,
Peter T

 

[filo666]

what does embedded means??? It is hard to me to belive that it is not
possible to change the size of a chart, could you answer me if it is possible
and how, your answer was not clear.

thanks.

 

[Peter T]

An embedded chart is a chart on a sheet as distinct from a chart-sheet.
Typically embedded charts are on Worksheets though could also be embedded in
a chart-sheet.

A Chart-sheet type chart is not directly resizable, its dimensions are
determined by PageSetup properties, page size and margins, which you can
change.

An embedded chart's size can be changed by changing the size of its parent
container object - ChartObject.

If the above is not clear explain what you have, a chart-sheet or embedded
chart, and for what purpose do you want to change its size. Refer also to my
previous post.

Regards,
Peter T

 

[filo666]

I defenetivly have a chart on a sheet because I have 4 different charts in
the same sheet and also the ploted data is in the same sheet.
how to programatically change the size???

 

[filo666]

This worked perfectly:
Private Sub CommandButton1_Click()
On Error GoTo eh
ActiveChart.Parent.Width = CLng(TextBox1)
ActiveChart.Parent.Height = CLng(TextBox2)
eh:
End Sub

thanks for your help

 

[Peter T]

I gave you the answer in my first post, change

ActiveChart.ChartArea.Width = CLng(TextBox2)

to
ActiveChart.Parent.Width = CLng(TextBox2.Text)

When the ActiveChart is an embedded chart its parent is a ChartObject, so
you could also do say

ActiveSheet.ChartObjects(1).Width = 200
or
ActiveSheet.ChartObjects("Chart 1").Height = 150

or you could even change all in one go

ActiveSheet.ChartObjects.Height = 100 ' assumes at least one chartobject

Regards,
Peter T

 

[Jon Peltier]

A Chart-sheet type chart is not directly resizable, its dimensions are
determined by PageSetup properties, page size and margins, which you can
change.

That's what I was going to say, but I tried the OP's code and learned that
the chart area is resizable in a chart sheet. The code he posted works fine
for a chart sheet only.

- Jon