Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts and Userforms
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts and Userforms
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts and Userforms
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts and Userforms
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Charts and Userforms
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 In article .com, says... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForms | Excel Discussion (Misc queries) | |||
Esc out of userforms | Excel Programming | |||
Charts & Graphs on Userforms. | Excel Programming | |||
Userforms | Excel Programming | |||
Userforms | Excel Programming |