Excel Macro

  sms 09:45 28 Dec 2003
Locked

I am running an ecxel macro.
I would like the macro to run automically every 2-3 seconds after it has started.
Could anyone advise me hoiw to do this

  VoG II 09:49 28 Dec 2003

It will be just like the stopwatch macro that I gave you except that instead of putting the time in a cell it will have a line like

Call Mymacro

  VoG II 11:08 28 Dec 2003

Or something like

Sub runmymacro()

Do

Beep

Application.Wait Now + TimeValue("00:00:02")

Loop

End Sub


========================

substituting the name of your macro for "Beep".

I have to say that I hate this sort of code - it is an infinite loop. To get out of it you have to keep pressing the ESC key - unless you've disabled that in your macro.

  sms 13:09 28 Dec 2003

VoG
Set up the macro as you said.
You were right. It locks up everything while the macro is running. Will have to think again.
Thanks for the info anyway.

  tbh72 13:13 28 Dec 2003

The other option is to run your current macro at request, you can assign either a shortcut key or button.

  reddwarfcrew 13:30 28 Dec 2003

could you not enter a validation:

ie have cell a1 as the validation with a value of Y to run the loop and anything else to not run the loop and then enclose the loop statement in an if statement so that if cell a1 has a value of Y it runs the loop, if not it just goes to the end of the macro.

  reddwarfcrew 13:38 28 Dec 2003

I have got the same senario in a file at work and it doesn't lock up my PC... can't quite remember how I wrote it though and I'm not back in the office until 05/01/04. I think it was basically as Vog has said, but I'd used the GoTo code instead of loop, so when it got to the end it went back to the top.

Perhaps its locking because its every 2 secs, by the time the macro has run, the 2 secs has probably passed so it runs again. What happens if you set it to say 30 secs?

  VoG II 13:42 28 Dec 2003

It will stop if you keep tapping the ESC key - unless you've disabled the keyboard in the macro that you are calling.

  sms 15:10 28 Dec 2003

Reddwarfcrew-
would like to try the goto method if you can get it to me. If you could give me more details as I am not an expert in writing macros.
Have tried various times in the macro, but while it is wating for the time everything locks.

  VoG II 15:15 28 Dec 2003

Sub runmymacro()

Start_here:

Beep

Application.Wait Now + TimeValue("00:00:02")

Goto Start_here

End Sub

=============================

I don't see why this would make any difference.

  VoG II 15:26 28 Dec 2003

Sub runmymacro()

Call MeMacro 'put in its real name here

Application.OnTime Now + TimeValue("00:00:02"), MeMacro 'real name goes here as well

End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?