PDA

View Full Version : Solved: Save chart as image plus refresh



f2e4
07-15-2010, 08:54 AM
Setting the scene:
I have a table of data linked to a chart.
I have a slider linked to a cell (A1)
As the slider is moved, all of the values change, in turn changing the chart.

Challenge:
Every time the slider changes cell 'A1', take an image of the chart and paste to the right of it. This is to enable the user to compare the two.
When the cell A1 is changed again the image needs to be refreshed with the previous state

Note: I would upload an example but I'm posting this topic from my phone and can't access my docs.

Thanks again for the help.

jolivanes
07-15-2010, 07:58 PM
Would it not be easier to have two identical charts where the second chart has the same values minus the slider difference?
Or did I misunderstand the question?

John

f2e4
07-16-2010, 01:23 AM
Would it not be easier to have two identical charts where the second chart has the same values minus the slider difference?
Or did I misunderstand the question?

John

This option probably wouldn't work because I would need an additional table to store the difference values and a final table to store the previous values. Then as the slider moves all 3 tables values would change.

I've seen code before that can copy a chart as a picture but can't remember where I saw it. Once copied, the image (8cm x 10cm) needs to be pasted to the right of the the live chart.

Just as the slider is moved, the image needs to be refreshed (probably deleted and replaced with new image) with what the chart looked like before the slider was pressed.

Any ideas?

f2e4
07-16-2010, 06:54 AM
OK so I have figured out the 1st challenge of copying the charts and pasting as an image in the location I need.

Code as follows:
Sub Chart_Pics()
Application.ScreenUpdating = False
'Copy 1st chart and paste as image in cell M65
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
ActiveSheet.Range("M65").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
Selection.Name = "Image01"
'Copy 2nd chart and paste as image in cell M86
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
ActiveSheet.Range("M86").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
Selection.Name = "Image02"
'Copy 3rd chart and paste as image in cell M107
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.CopyPicture Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture
ActiveSheet.Range("M107").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
Selection.Name = "Image03"
'Move top right corner of image to cell W65
ActiveSheet.Shapes("Image01").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 237.75
Selection.ShapeRange.IncrementTop -3.75
Selection.ShapeRange.IncrementLeft 6
'Move top right corner of image to cell W86
ActiveSheet.Shapes("Image02").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 237.75
Selection.ShapeRange.IncrementTop -3.75
Selection.ShapeRange.IncrementLeft 4.5
'Move top right corner of image to cell W107
ActiveSheet.Shapes("Image03").Select
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 237.75
Selection.ShapeRange.IncrementTop -3.75
Selection.ShapeRange.IncrementLeft 1.5
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Now all I have to figure out is how to link this code to run every time the slider is changed