We help IT Professionals succeed at work.

Sequencially copy values to single cell in timed order

capital052798
on
I have sheet1 cell C1 that I wish to populate with values.
The values are in sheet2 - in the range "values" - this is a single column of data.
I think some VBA is required.
I would like the first value from "values" to be copied into sheet1 cell C1, this value needs to reside there for a specified amount of time say 10 seconds and then be replaced by the second value from "values" a pause and then the third value is put into cell C1  etc. etc.
The standard pause period needs to be controlled by the user who would specify the number of seconds delay in sheet1 cell A1.
There also needs to be a command button to temporally halt the feed process.
For info
The value in C1 governs the display of a graph of data.
I would like the graphs to be displayed automatically after allowing a sufficient amount of time to look at each graph. Where a graph has unexpected results - the use of a button to temporally halt the process would be usefull. Clicking the button again would then continue with the graph displays i.e.feeding data from "values" to cell C1.
Comment
Watch Question

Hi capital,

First of all, you need to create a little command button from the Forms toolbar on your sheet 1.

Then put this code in a module:

Public RunWhen As Date

Sub Button1_Click()
Static checkState As Boolean
If checkState = False Then
    'Launch the automatic times macro
    checkState = True
    AutoLoop
ElseIf checkState = True Then
    checkState = False
    Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoLoop", Schedule:=False
End If
End Sub

Sub AutoLoop()
Static counter As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
Set ws1 = ActiveWorkbook.Worksheets("Sheet1") 'Change to your sheet name
ws1.Range("C1") = ws2.Range("values").Cells(counter + 1, 1)
counter = counter + 1
If counter > ws2.Range("values").Cells.Count Then
    MsgBox "Graph will restart with first value"
    counter = 0
End If
RunWhen = Now + TimeValue("00:00:" & Format(ws1.Range("A1"), "00"))
Application.OnTime RunWhen, "AutoLoop"
End Sub

This should do the job.

More info needed, please ask back.

calacuccia

Author

Commented:
calacuccia
Thanks for the code - its sort of working but not properly.
If I put in cell A1 (for seconds) at say 10 then I get the correct feed BUT if I then use a lower number (for seconds) the data feed seems to miss a row i.e. if I have 1,2,3,4,5 in "values" (as a column of data) then I get 1,3 and then 5 coming through to C1. 2 and 4 are missed out.
Button1 starts the process off OK but Button1 on subsequent click (to pause) causes an error it highlights
"Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoLoop", Schedule:=False"

Re the Buttons - could I have
one to start the process
one to pause the process and
one to halt the process
I don't need the routine to repeat through as a loop.
Thanks
Hi Capital,

First about your 'missing rows' problem....

The reason is that there is a calculation process on the background, which makes Excel miss certain events apparantly. I presume your C1 cell makes a big change for the graph and results in a long calculation...

I will advise you to try the slightly altered procedure below, which makes Excel recalculate before sheduling a new update.

Other errors you got ....

The Error it yells, might be because you forgot the line

Public RunWhen As Date

in the beginning of your module.

Now, as you are also requesting three buttons, let's look at this:

1/ Paste code below (completely) into an empty module or over the module you had created from my first comment
2/ Create three buttons, and assign respectively the macros 'StartProcess', 'Pause_Restart' and 'Halt_Process' to them.
3/ Make sure you don't forget the first line (Public .... As Date as mentioned above

'Start code

'Public declarations, important for schedule time
Public RunWhen As Date
Public counter As Long
Public checkState As Boolean

Sub Pause_Restart()
If checkState = False Then
   'Launch the automatic times macro
   checkState = True
   AutoLoop
ElseIf checkState = True Then
   checkState = False
   Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoLoop", Schedule:=False
End If
End Sub

Sub AutoLoop()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
Set ws1 = ActiveWorkbook.Worksheets("Sheet1") 'Change to your sheet name
If counter + 1 > ws2.Range("values").Cells.Count Then
    MsgBox "End of list reached"
    counter = 0
    checkState = True
    Exit Sub
End If
ws1.Range("C1") = ws2.Range("values").Cells(counter + 1, 1)
Application.Calculate
counter = counter + 1
RunWhen = Now + TimeValue("00:00:" & Format(ws1.Range("A1"), "00"))
Application.OnTime RunWhen, "AutoLoop"
End Sub

Sub StopProcess()
counter = 0
Application.OnTime EarliestTime:=RunWhen, Procedure:="AutoLoop", Schedule:=False
checkState = True
End Sub

Sub StartProcess()
counter = 0
checkState = True
AutoLoop
End Sub
'End of code

Good Luck
calacuccia

Author

Commented:
calacuccia
Many thanks - the code now works fine.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.