Pause macro without pausing Excel

[langlang98]

I am looking to do a macro that cycles through a chart updating it with a slight delay. The example is I have 5 customers and I want a chart to cycle through each customer displaying their sales on a chart for 3-5 seconds each. I want the chart to pause between the update but still allow the spreadsheet to be edited/scrollable. Below is the VB code that I can find but it actually pauses Excel completely. I am advanced in the front end of excel but relatively new to the macro/VB side. I usually can take what someone has as an example and apply it to what I need but all the recommendations for the "Application.Wait" examples does what I want with the chart but does not allow movement in the spreadsheet.

Sub chartloop()
Dim i As Integer
i = 0
Do Until (i = 5)
i = i + 1
Range("B10") = i
Application.wait Now() + TimeValue("00:00:03")
Loop
End Sub

While I am at it, you'll notice that the code only cycles through 5 times, I haven't looked yet but is there a way to reset the "i" to 0 after x number of cycles?


Thanks,
Jim L.

 

[Andrew Poulsom]

Sorry, you can't interact with Excel while a macro is running, even if it's paused. You could use the OnTime method to call a procedure after a pause.

 

[VoG]

You can try this but scrolling will be jerky

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) ' must be at top of module


Sub chartloop()
Dim i As Integer
i = 0
Do Until i = 5
    i = i + 1
    Range("B10") = i
    Sleep 3000
    DoEvents
Loop
End Sub

 

[langlang98]

Thanks for the help guys. I think I am just going to rethink my strategy and go another route as it appears that I can't truly do what I was hoping to. But this is definitely good knowledge for some other things I want to try.
Thanks again!
Jim