Dynamically Changing Cell Value at a Constant Interval, With No Timer ??

Question

(posted on MrExcel Forum as well)

Hello;

1) A single value between 90. and 270. is manually entered into cell E15 on "Sheet1".

The corresponding display is Chart "Fig Projections".

2) It would be a good idea and very helpful if the chart is somewhat "animated".

By that I mean: instead of the current "static" chart corresponding to a single entered value (E15), replace it with a "dynamic" chart showing successive displays; each at an automatically changing E15 (with a constant interval, say, 5).

That's to say: 1st display would be for E15=90., then replaced with the display for E15=95., then for 100., 105., ..., 260., 265., 270., then repeat for 90., 95., 100., ..., etc. until the chart is no longer active.

3) Can one achieve that without the use of a timer (2 sec) ??

4) Are Tools::Customize::Menu animations relevant in this case ??

Your help, suggestions, comments would be greatly appreciated.

Answer 1

Andreas;

Thank you for your tremendous help in resolving the issue.

Here's a summary of what to do to Suppress the Jiggle/Flicker running animation (XL 2003):

1) The initial XL 2003 (flickering problem) w/b consists of a worksheet (data) and a Chart in its own sheet

....> add a new w/s and place the chart in it (instead of chart in its own sheet. Right-click the chart and select Location)

....> name the new w/s as the replaced chart sheet (for consistency)

....> add a resize routine in w/s Activate event to scale the placed chart (to full window)

....> add Chart Activate event to freeze animation if you click the chart anywhere

....> add a line to the animation routine Sub Diagramm1_Beiklick() to deactivate the chart to suppress flickering

(refer to Andreas above code, Nov 04, 2010)

2) Click the animation macro button on the chart

.... Press Ctrl+Break to STOP Animation

.... Click chart Or Select another cell to FREEZE Animation

The chart would repeatedly "resize" when animation is interrupted by clicking the chart followed by switching between the 2 w/s.

One needs to STOP the animation (Ctrl+Break) to properly end the session.

3) For those who prefer XL 2007:

The initial (problem) XL 2003 animation w/b (with the chart in its own sheet) would run quietly in XL 2007.  No flickering/jiggling during the animation!

Furthermore, should one follow the steps in Item 1 above, the chart would NOT repeatedly "resize" when animation is interrupted by clicking the chart (or selecting another cell) followed by switching between the 2 w/s.

Problem solved!

Thanks again.

Monir

Answer 2

2) It would be a good idea and very helpful if the chart is somewhat "animated".

By that I mean: instead of the current "static" chart corresponding to a single entered value (E15), replace it with a "dynamic" chart showing successive displays; each at an automatically changing E15 (with a constant interval, say, 5).

That's to say: 1st display would be for E15=90., then replaced with the display for E15=95., then for 100., 105., ..., 260., 265., 270., then repeat for 90., 95., 100., ..., etc. until the chart is no longer active.

We can do that in a similar way as you want.

Add this code to the code module of that sheet:

--- schnipp ---

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  CancelChartAnimation = True

End Sub

--- schnapp ---

And all code below my name to a normal module and assign the macro Diagramm1_BeiKlick to the chart.

Click the chart to start the macro and look at the statusbar too.

Andreas.

Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public CancelChartAnimation As Boolean

Sub Diagramm1_BeiKlick()

  Const WaitTime = 2000 'milliseconds = 2 seconds

  Const SleepTime = 10 'milliseconds (do not change)

  Dim R As Range

  Dim Start As Double, Ende As Double, Increment As Double, Value As Double

  Dim I As Long

  'Setup interacting cell

  Set R = Range("E15")

  'Read in the start value

  Start = R

  'Setup end value and increment

  Ende = 270

  Increment = 5

  'Enable exit handler

  Application.StatusBar = _

    "Press Ctrl-Break or select another cell to interrupt animation"

  Application.EnableCancelKey = xlErrorHandler

  On Error GoTo ExitPoint

  'Start endless loop

  CancelChartAnimation = False

  Do

    'Do the repetition

    For Value = Start To Ende Step Increment

      'Store new value into the cell

      R = Value

      'Start a wait cycle

      For I = 1 To WaitTime \ SleepTime

        'Let the chart change

        DoEvents

        'Worksheet_SelectionChange in affect?

        If CancelChartAnimation Then GoTo ExitPoint

        'Sleep a short time

        Sleep SleepTime

      Next

    Next

  Loop

ExitPoint:

  Application.StatusBar = False

  If Err = 18 Then

    'Restore value if Ctrl-break is pressed

    R = Start

  End If

End Sub

Answer 3

Am 04.11.2010 17:11, schrieb monirg:

Fantastic! Your code works perfectly and almost 100% as desired.

Thank you.

1*) In setting up the interacting cell*E15, the code statement should instead read:

.... Set R = Worksheets("Sheet1").Range("E15")

Well, I can not see your workbook and I've assumed that the data and the chart is on the same sheet. If so, Worksheets("Sheet1") is superfluous, because the macro did only run if Sheet1 is active. .-)

*2*) Since Sheet1 is protected, only the 1st part of the statusbar info applies.  It should instead read:

.... Application.StatusBar = "*Press Ctrl+Break to interrupt animation*"

How should I know that the sheet is protected?

*3*) There's a problem!

The chart jiggles/flickers during the execution of your macro at the start of each new display.

Everything flicker; the plot, the plot area, the chart title, the legend box, the macro button, etc.

You probably noticed: "..., With No Timer ??*" in the title of my*OP,

And how do you want to wait 2 seconds without a timer?

Anyway, the timer is not the problem, it's the chart itself or some formula calculation or other events that are in effect.

Try it with an empty workbook, write 0 in E15, add a chart with that data to the sheet, assign the macro, etc. there is no flickering.

*4*) I've added at the top of your w/s *Selection_Change*() event:

.... *Application.ScreenUpdating = False

Screeupdating is the wrong way, because if you enable it excel redraws the whole screen. Maybe

  Application.EnableEvents = False

  Application.Calculation = xlCalculationSemiautomatic

helps, but if your chart is huge or you have more then one chart to animate at the same time, you must go other ways.

I've written a chart animation for a medical project a few years ago that shows 5 animated charts at the same time and I can remember that I've had the same flicker problem. As far as I remember the solution was to calculate the data completely before the animation and then change the formula in the chart so that it "moves over" the data.

If you like visit my page http://home.arcor.de/andreas.killer/ and download the file chart.avi (4,8Mb) which shows the animation. Before you ask: I can not give you that excel file.

Andreas.

Answer 4

Andreas;

Fantastic! Your code works perfectly and almost 100% as desired.

Just couple of typos:

1) In setting up the interacting cellE15, the code statement should instead read:

... Set R = Worksheets("Sheet1").Range("E15")

2) Since Sheet1 is protected, only the 1st part of the statusbar info applies.  It should instead read:

... Application.StatusBar = "Press Ctrl+Break to interrupt animation"

I reckon one could (in addition) use the Chart_activate() event to stop the animation.

I very much liked your ingenuity in predicting that restoring the start value toE15 if Ctrl+Break is pressed would be a good idea.

Perfect!

3) There's a problem!

The chart jiggles/flickers during the execution of your macro at the start of each new display.

Everything flicker; the plot, the plot area, the chart title, the legend box, the macro button, etc.

You probably noticed: "..., With No Timer ??" in the title of myOP, because I was concerned about such flickering and its harmful effects on the eyes.  You'd feel drowsy after few seconds!

4) I've added at the top of your w/s Selection_Change() event:

... Application.ScreenUpdating = Falsebut it didn't suppress this jiggle.

(I'm assuming that ScreenUpdating defaults back to True when the macro terminates its execution.)

Any thoughts/fix ??

Regards.

Monir

Answer 5

Andreas;

Thank you kindly for your prompt reply. Lot of work in almost no time!  Greatly appreciated.

Please allow me some time to implement/test your procedure and to understand how it works.

(I've just retrieved your reply.  There was no "Alert Notification" this time.)

Regards.

Monir