Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graph As Status Bar
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Graph As Status Bar
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 wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining a 2-D column graph and a 2-D line graph on the same char | Charts and Charting in Excel | |||
reuest formula for auto update status & status date | Excel Worksheet Functions | |||
How do I graph data daily as a line graph across a calendar format | Charts and Charting in Excel | |||
Powerpoint Graph- Shading in between two linear graph lines | Charts and Charting in Excel | |||
Hyperlinkage of one graph with another graph or Drill down graph | Charts and Charting in Excel |