Solved: Chart on a UserForm

[SilverBack]

Hey all, I'm working on a class assignment. I'm working the bugs out of the requirements, and trying to add an extra feature at the same time. I'd like some help with the extra. The extra isn't a requirement, and won't affect my grade unless he decides to award cool points.

I'd like to get a chart to appear on a UserForm, and update real-time. The instructor and his TA say it's a lot of trouble and isn't required, but I'd like to give it a shot anyhow. I haven't seen anything like this in my books, so I don't even know if it's possible. Can anyone give me an example?

Thanks!

[johnske]

You'll need Office 2000 or better. Insert a userform then go to Tools > Additional Controls > Microsoft Office Chart 9.0 (this sets a reference to Microsoft Office Web Components 9.0) then select the chart icon on the toolbox and insert your chart on the userform.

Now read Xl-Dennis' article to find out how to populate the chart http://www.vbaexpress.com/forum/arti...ticle&artid=47

(Never tried this before so keep us updated )

[SilverBack]

Being as I have never seen any of that before, I think I'll stick with just what's required


Thanks again!

[johnske]

Being as I have never seen any of that before, I think I'll stick with just what's required


Thanks again!

Awwwww... hopin' to see someone do an example of this (have we got a 'chicken' smiley? )

[Justinlabenne]

I can't say I have ever developed my own method, I always use Stephan Bullen's PastePicture code to do most of the work: or of course J-Walk's method

[SilverBack]

Well, if I had more than one semester of experience doing this, and wasn't running a farm, and building equipment, and had more than a week and a half to finish two assignments for my college class, I'd sure give it a shot!

[Dave]

I see that you have solved this but I thought I would post some code that may get you started towards that "A". This approach uses J-Walk's method to produce a GIF on a userform. This code produces it's own changing data by increasing the iterations of a function which is then charted every 2 seconds as the same chart but with more interations. If you had changing real time data to chart I think this code could probably be adapted to produce real time chart images on a userform. This code requires a userform(Userform1) with a image control(Image1). Data is placed on Sheet1("A"&"B"). The changing iterations are stored in Sheet1!C1. Note that this code is recursive and requires a method to shut it off (C1 is also used for this). HTH. Dave
In a module...
[VBA]
Public Sub ChartFunction()
'charts function on sheet 1
'creates GIF image file of chart. Removes chart and series data
Dim ChartRange As Range, Xvalue As Range, Yvalue As Range, Increment As Double
Dim Xmin As Double, Xmax As Double, Iter As Integer, Cnt As Integer, Fname As String
[sheet1!C1] = [sheet1!C1] + 10
Iter = [sheet1!C1] 'number of chart points
Xmin = -2 'lowest "X" value
Xmax = 1 'highest "X" value
Increment = (Xmax - Xmin) / (Iter - 1) 'chrt pt increments
'make chart data
For Cnt = 1 To Iter
Sheets("Sheet1").Cells(Cnt, 1) = Xmin '"X" value
' "Y" value generated by function for "X" value eg. Exp(X) * Sin(X) ^ 2)
Sheets("Sheet1").Cells(Cnt, 2) = Exp(Xmin) * Sin(Xmin ^ 2) ' "Y" value
Xmin = Xmin + Increment
Next Cnt
'make chart
Set Xvalue = Sheets("Sheet1").Cells(1, 1)
Set Yvalue = Sheets("Sheet1").Cells(Iter, 2)
Set ChartRange = Sheets("Sheet1").Range(Xvalue, Yvalue)
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
'create image file,delete chart, delete data series
Fname = ThisWorkbook.Path & "\" & "ChartF(x).gif"
ActiveChart.Export Filename:=Fname, FilterName:="GIF"
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
ChartRange.Clear
Call Repeat
UserForm1.Image1.Picture = _
LoadPicture(ThisWorkbook.Path & "\" & "ChartF(x).gif")
End Sub
Public Sub Repeat()
'charting iterations
If [sheet1!C1] <= 100 Then
Application.OnTime Now + TimeValue("00:00:02"), "ChartFunction"
Else
MsgBox "Charting done"
End If
End Sub
[/VBA]

In Userform code...
[VBA]
Private Sub UserForm_Initialize()
[sheet1!C1] = 0
Call Repeat
End Sub
[/VBA]

To operate...
[VBA] UserForm1.Show[/VBA]

[SilverBack]

The reason I marked this thread as solved is because this is way over my head. I could probably copy and paste the code, but if I was asked to recreate it I couldn't. I just decided to leave out the chart

Thanks though!

[SherryO]

I know you marked it as solved, but I thought you may like to know how easy it can be... Good Luck with school:>
[VBA]Private Sub UserForm_Initialize()
Call CreateChartMO
End Sub
Sub CreateChartMO()
Dim CurrentChart As Chart
Dim Fname As String
Set CurrentChart = Sheets("graph").ChartObjects(1).Chart
Fname = ThisWorkbook.Path & "\temp.gif"
CurrentChart.Export FileName:=Fname, filtername:="gif"
imMarket.Picture = LoadPicture(Fname)
End Sub[/VBA]