Start a macro from a change in a cell value made by a formula?

[Geejay]

I have a macro that records, 5 cells of data from an internet source and then
drops it down a line ready for the next time I push the button.

I would like to automate this procedure, recording every 20 seconds for
about 20 mins. I have created a timer that returns a '1' every 20 seconds and
a zero otherwise. The '1' is visible for one second.

I need help in marrying the timer,sourced from a formula, to the macro.

Can anyone help, bearing in mind that I am new to this.

 

[Mike H]

Hi,

It sounds line you need application.ontime

Once you start it running the code below will call itself recursively every
20 seconds until you stop it with CTRL+Break. There are programmatic ways of
stopping it if you want to develop this

Public RunTime As Date
Sub MyCode()
RunTime = Now + TimeValue("00:00:20")
Application.OnTime RunTime, "MyCode"

'YOUR CODE


End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Geejay]

Hi Mike,
Many thanks for taking an interest in my problem, it is much appreciated.

I have added your code in above mine and will be able to test it on Monday.
I will let you know how I get on.

This is how it looks:

Public RunTime As Date

Sub MyCode()
RunTime = Now + TimeValue("00:00:20")
Application.OnTime RunTime, "MyCode"

Sub Macro8()
'Macro8 Macro
Range("D2:H2").Select
Selection.Copy
Sheets("Records 2").Select
Range("A2:E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Many thanks once again.
Geejay

 

[helene and gabor]

Hello,

I am answering your query without much experience.
My Excel 2007 allows me to specify Data, Properties and on External Data
Range Properties the refresh periods.


Good Luck!

Gabor Sebo

 

[Geejay]

Hi Mike,
Got excited that I had a reply and sent wrong code, please see below for
correct code.

Ctrl+Break (pause break) does not stop it running however, when I start the
macro from a button. Will try using a togglebutton instead. It is running
well on tests so far. Will report on live test on Monday.

Public RunTime As Date

Sub Macro8()
RunTime = Now + TimeValue("00:00:20")
Application.OnTime RunTime, "Macro8"
Sheets("Control Panel").Select
Range("D2:H2").Select
Selection.Copy
Sheets("Records 2").Select
Range("A2:E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Once again many thanks for your help.
Geejay

 

[Geejay]

Hi,
The code runs very well but, i am unable to stop it.
Ctrl + Break has no effect.
Any ideas please?
If I know how to stop it I can try putting it on a toggle button, maybe!

kindest regards
Geejay.

 

[Chip Pearson]

Nope. CTRL Break won't cancel an OnTime event. It might break in the
called procedure if it happens to be running when you hit Break, but
that has nothing to do with OnTime.

The code runs very well but, i am unable to stop it.

Sub StopIt()
Application.OnTime RunWhen, "ProcName", , False
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Geejay]

Hello to Mike, Chip and Gabor,
Many thanks for your interest in my problem.
I changed Mikes code from 'Now' to 'Now()' and added Chips code.
All is now running smoothly from a pair of buttons. Nice to know there are
generous people like yourselves out there.
Many ,many thanks.
Kindest regards,
Geejay.