Graph As Status Bar

[terryspencer2003]

I have set up a simple graph on Sheet1 (i.e. Chart1). I have filled
the series with RAND() functions so that when I calculate the
spreadsheet, the graph updates upon calculate.

When I hit F9 and hold it, the graph moves with the new values
generated by the Rand() functions (like an animation). I now want to
mimic this by sending the graph to a form while in a ForNext loop. So
as I loop, I will have a floating form which shows a graph with
changing values. Kind of like a status bar. Always there showing new
values. So what I have done is tried to use John Walkenbachs code for
charts in userforms. I want to send my chart to a userform while
looping through a variable. The difference here is that I don't have
any buttons on my form and I am only loading one graph. When I try
the code, the userform pops up with the chart, but then stays on the
screen unchanged. The loop does not progress passed the first
increment. If hit the X on the graph and collapse it, the loop
progresses and reloads a new graph. What do I have to do to keep this
userform updating? I am assuming that I have to either put in a Hide
statement or an Unload statement somewhere in the loop. Can anyone
help me with this?

Option Explicit

Sub LoopThing()
Dim X As Double

For X = 1 To 100000
Calculate
ShowChart
Application.StatusBar = X / 100000 * 100 & "%"
Next X
End Sub

Sub ShowChart()
UserForm1.Show
End Sub

Option Explicit

Dim CurrentChart As Chart
Dim Fname As String
Private Sub UserForm_Initialize()
UpdateChart
End Sub

Private Sub UpdateChart()
Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
CurrentChart.Parent.Width = 300
CurrentChart.Parent.Height = 150

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

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

End Sub

 

[Robin Hammond]

I don't think you can using this technique.

The chart is on the form as a picture, not as a live object. Furthermore,
the userform is in control of the application once you launch it.

What you could do is trap worksheet calculation events if the form is
modeless then update the chart after the calculation is finished.

The only potential solution for your aim that I can think of is this:
If you want the form to be there while the sheet calculates, you can use
frm.show vbmodeless in excel 2000 and higher. This would keep the sheet
active, rather than the form. You would then need to set a high resolution
timer using API calls that would launch a routine to update the picture for
the chart, but whether this would fire reliably while excel is calculating
is purely a guess. I think there was something about these api calls on Chip
Pearson's site. To avoid the slow way that the chart is put on the form with
an export to a file and back as a pic, have a look at Stephen Bullen's
PastePicture routine.

All in, it seems like a lot of work for something a bit arcane.

Robin Hammond
www.enhanceddatasystems.com