Charts and Userforms

[julia.stone]

I am new to VBA for Excel, and I need some assistance with the
following. Any help would be greatly appreciated.

On one worksheet in my workbook I have 5 charts. On another worksheet
I have 5 option buttons, and for each button clicked I would like to
display the chart in the upper left (from cell A2) on my worksheet
(just above the option buttons). I am currently able to display the
charts on a userform, but even though I have modified the properties of
the form it has borders ... and I would like for the form to appear to
be resident on my worksheet just above my option buttons. And I would
like the form to appear with out borders. Also, when the graphs are
displayed in the userform they aren't so clear (I tried various
property settings for PictureSizeMode).

I read about transparent forms, and I am not sure if this is exactly
the way I should go with this. Can anyone share some information for
me that might let me know how to accomplish this task.

Thanks in advance,
Julia

 

[OJ]

Hi,
I don't think you need to use a userform. Try something like this....


Private Sub CommandButton1_Click()
Sheet1.ChartObjects(1).Copy
Me.Range("a2").Select
ActiveSheet.Paste
End Sub


This will paste the chart from sheet1 to the current sheet at A2....you
could add code to remove any chart which is already there...
Hth,
OJ

 

[julia.stone]

Thanks a lot. It works, but I need to modify my chart size. I would
like my chart to fit in the range of A2:H24. How can I make sure my
chart always fits in that range?

I also implemented the code to delete a chart that is already there.
Thanks for the tip.

After trying your code sample ... the loading of the chart is a bit
slower than with a userform. Is there anything else I could implement
that might help?

Thanks,
Julia

 

[donna.gough]

Julia / OJ

It may be worth trying to find a way that doesn't copy/paste the chart.
I am saying this as i am having memory error problems copying/pasting
charts.
Admitedly I am copying and pasting a vast number of charts - not 1 or 2
but I have enough memory in my PC, it seems to be that Excel is not
very good at handling and reusing the memory when copying and pasting
charts.

What to other people think?
Donna

 

[Tushar Mehta]

If you use an image, it's faster but it also leaves a transparent
'border.' If you use a chart, it is fractionally slower but can be
aligned flush with various edges.

To align the copied object with top-left of A2 and bottom-right of H24,
use:

Sub testPicture()
Worksheets("sheet1").ChartObjects("Chart 1").Chart.CopyPicture _
Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
With Worksheets("Sheet2")
Do While .Shapes.Count > 0: .Shapes(1).Delete: Loop
.Paste
.Shapes(1).Left = .Range("a2").Left
.Shapes(1).Top = Range("a2").Top
.Shapes(1).Width = Range("i24").Left - Range("a2").Left
.Shapes(1).Height = Range("h25").Top - Range("a2").Top
End With
End Sub
Sub testChartObject()
Worksheets("sheet1").ChartObjects("Chart 1").Copy
With Worksheets("Sheet2")
Do While .ChartObjects.Count > 0: .ChartObjects(1).Delete: Loop
.Paste
.ChartObjects(1).Left = .Range("a2").Left
.ChartObjects(1).Top = Range("a2").Top
.ChartObjects(1).Width = Range("i24").Left - Range("a2").Left
.ChartObjects(1).Height = Range("h25").Top - Range("a2").Top
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005