Make a record of a excel cell (1 Viewer)

scottappleford

Registered User.
Local time
Today, 10:24
Joined
Dec 10, 2002
Messages
134
Hi

I have a formula constantly calculating in Cell M3

I would like to make a record of this every 30 seconds in column P

Thanks
 

Ranman256

Well-known member
Local time
Today, 06:24
Joined
Apr 9, 2015
Messages
4,337
you mean ,
copy the current value in M3 to another page
then repeat every 30 secs?
 

scottappleford

Registered User.
Local time
Today, 10:24
Joined
Dec 10, 2002
Messages
134
hi

yes this is correct please

it copies the content of M3 (this is calculating based on a fomula) and copies it to column P is fine and keep a history - so copies to next available cell in column P - can start at Cell P1

many Thanks
 

scottappleford

Registered User.
Local time
Today, 10:24
Joined
Dec 10, 2002
Messages
134
Hi

sorry to be pain - can anyone help me with this one please

appreciate your help
 

Rx_

Nothing In Moderation
Local time
Today, 04:24
Joined
Oct 22, 2009
Messages
2,803
This is just from memory
Look up the Application.OnTime for Excel
It is somewhat like an Alarm Clock
In a Worksheet module - add the code.
Place the sstartTime procedure in the Auto_Open event
code something like this:
Code:
Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub

Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:03"), "Timer" 'ever 3 seconds
End Sub

Private Sub Stop_Timer()
    TimerActive = False
End Sub

Private Sub Timer()
    If TimerActive Then
        ActiveSheet.Cells(1, 1).Value = Time
        Application.OnTime Now() + TimeValue("00:00:03"), "Timer"
    End If
End Sub

Your Excel must be saved as a Macro Enabled
The knowledge of the Debugging Tools will be very important
The management of these events will be tricky!
1 trying to code and the focus on the window gets interrupted every time the event triggers.
2 When multiple workbooks open, you close the one that's supposed to use the timer, and it keeps triggering and reopening the workbook (forgetting to kill the event properly).

Suggest testing every 30 seconds until the code works correctly
 

scottappleford

Registered User.
Local time
Today, 10:24
Joined
Dec 10, 2002
Messages
134
Thanks for your help

i will have a play about and see how i get on
 

Users who are viewing this thread

Top Bottom