Adding Charts to Excel User Forms

[daseffects]

Is it possible to add Excel Charts to a user form? Possibly converting an existing chart to an image and inserting it?

I want to dynamicaly chart the impact of the values entered in the form - so that users can see the result of their input.

Thanks.

D

 

[MeGustaXL]

Hi there D!

John Walkenbach describes exactly how to do this here:

http://www.j-walk.com/ss/excel/tips/tip66.htm

You're right; it does involve exporting the chart as a GIF, then making an Image Object on the form display it.

On a fast PC the change is only just noticeable, but anything slower than about 450 MHz has irritating and uncool-looking flickerage as the GIF loads, accompanied by a progress bar showing the picture loading process.

If anyone knows how to smooth the process I'd love to hear of it!

Happy Charting!

Chris

 

[daseffects]

I just found that code see below modified for 2 images and the loading is annoying. There has to be a way to avoid it maybe by using a different image format?

I also came across some code from Stephen Bullen at Stephen@BMSLtd.co.uk that works (no flicker or load) but uses 1 page of code which I can't get my head around (newbie).

Any help in eliminating the load would be greatly appreciated.


Private Sub UpdateChart()
Application.ScreenUpdating = False
Set CurrentChart = Sheets("Events&quot.ChartObjects(1).Chart
CurrentChart.Parent.Width = 400
CurrentChart.Parent.Height = 200
' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image1.Picture = LoadPicture(Fname)

Set CurrentChart = Sheets("Events&quot.ChartObjects(2).Chart
CurrentChart.Parent.Width = 400
CurrentChart.Parent.Height = 200

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image2.Picture = LoadPicture(Fname)
Application.ScreenUpdating = True
End Sub

 

[MeGustaXL]

I haven't tried this myself, but how about adding:

Application.DisplayAlerts=False / True

when you turn OFF/ON ScreenUpdating?

I've had a look at Stephem's PastePicture.xls (if that's the one you mean) and I agree; it's super-smooth, but I haven't a clue how to pick out the bits that are needed to just copy a picture of the chart into the Image Control!

Maybe if Bowers or Dale Watson (or even Stephen himself!) see this they can shed some light - for both of us!

Good Luck,

Chris