Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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 "Mike H" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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 "Mike H" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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. "Mike H" wrote: 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" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
20 seconds until you stop it with CTRL+Break 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 On Mon, 15 Mar 2010 11:44:01 -0700, Geejay wrote: 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. "Mike H" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start a macro from a change in a cell value made by a formula?
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. "Chip Pearson" wrote: 20 seconds until you stop it with CTRL+Break 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 On Mon, 15 Mar 2010 11:44:01 -0700, Geejay wrote: 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. "Mike H" wrote: 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" wrote: 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I start a macro from a cell by using a formula | Excel Worksheet Functions | |||
Macro to change list box input range based on selection made in another cell | Excel Programming | |||
getting a macro to run when a change is made to a specific row | Excel Programming | |||
Can I automatically start a macro if the contents of a particular cell change | Excel Programming | |||
Start macro on cell change | Excel Programming |