Automating Macros

Jar2112

New Member
Joined
Dec 30, 2003
Messages
30
Does anyone know how I can set a macro to automaticly update with a set amount of time? Like every five minutes for example. I have the macro assigned to a button that I would like to not have to push constantly.

THANKS EVERYONE!!!! :pray:
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

scuzz

Board Regular
Joined
Feb 1, 2004
Messages
57
Maybe you could delay the time that the macro will run, and loop the statement making it repeat?? dont ask me how you'd do that tho!! :eek:
 

Ken Puls

Active Member
Joined
Jun 9, 2003
Messages
482
Try looking up OnTime either on this site, or in the VBA help. That should get you started.

HTH,
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Well, the 2 guys replied while I was writing up a simple example so I'll post it anyway:-

If you place all of this code into a module and run the procedure 'Main', the procedure DoSomething will run every 5 minutes. Call the StopIt procedure to cancel it.

Code:
Dim dteProcTime As Date

Sub Main()
    dteProcTime = Now() + TimeValue("00:05:00")
    Application.OnTime dteProcTime, "DoSomething"
End Sub


Sub DoSomething()
    Cells(1, 1) = "Hello.  The current time is " & Now
    Call Main
End Sub


Sub StopIt()
    Application.OnTime dteProcTime, "DoSomething", , False
End Sub
 

simes

New Member
Joined
Feb 27, 2004
Messages
1
came across this post as i have been trying to do the same thing...

didn't seem to work...or might have been me...

the macro seems to run only once as opposed to at regular intervals

pls help
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi Simes,

Are you running the "Main" macro? It should also work if you run the DoSomething macro so I'm not sure what you're doing wrong. Just to clarify, this macro will run every 5 minutes and will put the current time in cell A1 UNTIL you run the StopIt macro.

Let me know how you go mate
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top