"Dan" wrote in message
How about pasting Chart into UserForm/Picture Control? Is there another
way
to accomplish it
Pasting? Only with Stephen Bullen's approach. Otherwise the way you were
doing it with my suggested changes
other than Stephen Bullen's approach? His way is VERY GOOD
but too complex for me.
All you need to do is this -
Put Stephen's modPastePicture as-is into your project (you can drag it from
PastePicture.xls)
Add a userform with an image control named Image1
In the userform module -
' Public if need to update from outside the form with say a change event
Public Sub ChartToImage()
Dim MyChart As Chart
'<< CHANGE MyChart TO SUIT
Set MyChart = Charts(1)
'Set MyChart = ActiveSheet.ChartObjects(1).Chart
'Set MyChart = Charts("Tool Sales2")
MyChart.CopyPicture xlScreen, xlPicture, xlScreen
Set Image1.Picture = PastePicture(xlPicture)
End Sub
Private Sub UserForm_Initialize()
' set these at design time
Image1.AutoSize = False
Image1.PictureSizeMode = fmPictureSizeModeZoom
ChartToImage
End Sub
What could be simpler !
Regards,
Peter T
"Peter T" wrote:
If you have the Image's autosize property set as True, change it to
False.
Do not set PictureSisizeMode to 0.
If you have a large chart you may find Stephen Bullen's approach works
much
faster, and scales better with the metafile option. See PastePicture.zip
here
http://www.oaltd.co.uk/Excel/Default.htm
Regards,
Peter T
"Dan" wrote in message
...
Another question,
I need to set chart exported to a specific size to fit in
UserForm/ImageControl. Since the chat is large, only small portion of
of
is
being displayed. I tried other options of changing PictureSizeMode
between
0,
1 and 3, but this is not what I need. Anyone has ideas? I am using
John's
URL
http://j-walk.com/ss/excel/tips/tip66.htm to fit according to my need.
Code
is below.
Dim MyChart As Chart
Dim ChartNum As Integer
Private Sub UserForm_Initialize()
ChartNum = 1
UpdateChart
End Sub
Private Sub CloseButton_Click()
Unload Me
Kill (ThisWorkbook.Path & Application.PathSeparator & "temp.gif")
End Sub
'Declare Chart as variable
Private Sub UpdateChart()
Set MyChart = Charts("Tool Sales2")
' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
MyChart.Export FileName:=Fname, FilterName:="GIF"
' Show the chart
Image1.Picture = LoadPicture(Fname)
End Sub