smooth countdown

[plane_nerd]

greetings all, sorry if this is covered somewhere else if it is really basic.

i have a macro that loops through calculating to make a count-down timer work. the problem with it is that the screen flickers each time it calculates. It runs roughly like this:

Sub Countdown ()
For i = 1 to 100
Worksheets("Sheet2").Cells(i,1).Value = i
WaitHours = 0
WaitMins = 0
WaitSecs =1
Calculate
Next i
End Sub

Is there a way to smooth it out or make the code simpler?

Thanks in advance
K. B.

 

[jeffreybrown]

Hi & Welcome to the Board,

The typical way would be by adding application.screenupdating...

Sub Countdown()
    Application.ScreenUpdating = False
    For i = 1 To 100
        Worksheets("Sheet2").Cells(i, 1).Value = i
        WaitHours = 0
        WaitMins = 0
        WaitSecs = 1
        Calculate
    Next i
    Application.ScreenUpdating = True
End Sub

 

[plane_nerd]

thanks for reply so quickly jeff,

i tried something like that code but i need to see the effects of the countdown

thanks
K. B.

 

[jeffreybrown]

Can you please explain what you are trying to accomplish?

I run the code and it records 1 to 100 in A1:A100...

What is suppose to happen for you?

 

[plane_nerd]

i'll send you a private message with the workbook and more details

 

[jeffreybrown]

Please do not do that...I did not request the workbook.

If you could kindly explain on the message board what you are trying to do this does more than one thing.

First, others may like to follow this thread as it could help them with something they are working on...

Second, if you work on the message board this ensure a wider group of viewer's could potentially be looking at the query and maybe it can be resolved better or even more expeditously...

 

[plane_nerd]

sorry, i just sent it,

so what i want is a countdown, therefore, it needs to update each second. the macro that was in my original post was the only way i could get it to update each second. the main problem is when it calculates the screen flickers, which is what i want to avoid. application.screenupdating won't solve the problem because i need to see the result of each calculate

K.B.

 

[jeffreybrown]

This description still does not do it for me...

Can you try a clearer description which will explain the what, why, when, how, etc., and post the link to the workbook here...?

 

[plane_nerd]

what: calculate each second without the screen flickering
why: to have a smooth countdown
when: each second
how: through a macro

you can download the workbook here, don't worry, there aren't any viruses:
http://dl.dropbox.com/u/52928863/countdown.xlsm

thanks

 

[jeffreybrown]

Maybe something like this...

Sub ShowProgress()
    Dim lngLoop As Long
    For lngLoop = 1 To 100
        Sheets("Sheet1").Range("A1") = Now
        Application.Wait Now + TimeValue("00:00:01")
    Next lngLoop
End Sub

And maybe in the ThisWorkbook module...

Private Sub Workbook_Open()
    Sheets("Sheet1").Range("A1").Formula = "=NOW()"
End Sub

...that way when the workbook first open A1 would get the current time or you could just have it hard code the time on open