Chart Picture Size in UserForm/Picture Control.

[Dan]

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

 

[Peter T]

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]

How about pasting Chart into UserForm/Picture Control? Is there another way
to accomplish it other than Stephen Bullen's approach? His way is VERY GOOD
but too complex for me.
D.

 

[Peter T]

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

 

[Dan]

Wow! That is impressive! You are SMART! Not like me! Thanks a lot for
helping. I wish I knew as much as you do!
Dan.

 

[Dan]

Peter,
That was impressive, and one more question: when I run UserForm, the chart
is displayed in Image Control, but it is distorted (not sure if the word is
applicable). The legend is displayed on the right where it is supposed to be,
and then portion of the legend is also on the left side of image control
(adjacent to Frame control). Any ideas how this can be fixed? I tried to to
reduce size the image and frame control, but it didn't help. I am getting an
impression the Image control acts as distorting mirror. Hope to hear from you
soon.

 

[Peter T]

When you say "distorted" do you mean stretched / squashed vertically /
horizontally or do you mean fuzzy / not clear.

Add two buttons to your form and the following (adjust button names below if
necessary)

Private Sub CommandButton1_Click()
Dim va
Static i As Long
va = Array(0, 1, 3)
i = i + 1
If i > 2 Then i = 0
Image1.PictureSizeMode = va(i)
Me.Caption = "PictureSizeMode " & i
End Sub

Private Sub CommandButton2_Click()
Dim MyChart As Chart
Dim lPicType As Long
Static bType As Long

If bType Then
lPicType = xlPicture
Me.Caption = "xlPicture"
Else
lPicType = xlBitmap
Me.Caption = "xlBitmap"
End If
bType = Not bType

'<< CHANGE MyChart TO SUIT >>

Set MyChart = Charts(1)
'Set MyChart = ActiveSheet.ChartObjects(1).Chart
'Set MyChart = Charts("Tool Sales2")

MyChart.CopyPicture xlScreen, lPicType, xlBitmap
Set Image1.Picture = PastePicture(lPicType)
End Sub

If your chart is always the same "aspect" size, which if a chart sheet it
probably is, size your image similarly. Otherwise, if even changing
PictureSizeMode does not make it look right, resize your image to same
relative aspect size as the chart. A little bit of maths, you will want to
restrict to a maximum height or width size.

Regards,
Peter T

PS your previous kind accolades would be more appropriately addressed to
Stephen Bullen !

 

[Peter T]

If your chart is always the same "aspect" size, which if a chart sheet it
probably is, size your image similarly. Otherwise, if even changing
PictureSizeMode does not make it look right, resize your image to same
relative aspect size as the chart. A little bit of maths, you will want to
restrict to a maximum height or width size.

I meant resize programmatically

Peter T

 

[Dan]

Thanks anyhow for walking through it. I will send additional email to Stephen
Bullen for developing the module as well.
Dan.