VBA script stops when the workbook is not active.

G

grid

Board Regular
Joined
Dec 4, 2009
Messages
56
Hello,

First, happy new year. I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well.

If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break.

I have tried several scripts that have the same effect. I want the script continues normally with execution.

Code: 
Public RunWhen As Double
Public Const cRunIntervalSeconds = 5 ' 5 seconds
Public Const cRunWhat = "TheSub"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

Sub TheSub()
    MsgBox "Example: every 5 seconds I see this message. Here comes the 2nd procedure." 
    StartTimer  ' Reschedule the procedure
End Sub

Can someone give me a solution?


Thanks so much!

grid

:)
 
Last edited:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
grid

What is the code actually meant to do?

The only code you've posted is the code that appears to call another sub every 5 seconds.

Perhaps the problem is with the code in the sub that's being called?:)
 
Upvote 0
Hello Norie,

My script I use a routine to call. External data will then be picked up. The routine is thus in the place of MsgBox. This routine works well.

I keep looking for the solution to the timer to run continuously. So if my workbook is not for me. Or if I work with another program.


Best regards,

grid

:)
 
Upvote 0
Hello,

I want to run automatically every 5 seconds: ThisWorkbook.RefreshAll

So if anyone here has a way (perhaps using VBA). The renewal should be every 5 seconds to continue. So even if I work with a different workbook. Or with another program and Excel in the background.


Best regards,

grid

:)
 
Upvote 0
It has been resolved!


Code: 
Sub MySub()
ThisWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:00:05"), "MySub"
End Sub


Best regards,

grid

:)
 
Upvote 0
grid said:
I want to run automatically every 5 seconds: ThisWorkbook.RefreshAll
Click to expand...

Code: 
Sub starttimer()
Application.OnTime Now + TimeValue("00:00:05"), "NextProc"
End Sub

Sub NextProc()
ThisWorkbook.RefreshAll
starttimer
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
set timer with a variable
Replies
2
Views
130
bbxrider
B
JeffGrant
  • Question
Application.Ontime - just stops
Replies
2
Views
188
JeffGrant
JeffGrant
V
  • Solved
Workbook with Timer Macro Saving After Closing
Replies
1
Views
486
John_w
J
C
  • Question
VBA button click from within a macro
Replies
2
Views
559
Chris_yorks
C
L
  • Solved
VBA Run-time Errors
Replies
3
Views
554
StephenCrump
S

Forum statistics

Threads
1,215,848
Messages
6,127,275
Members
449,372
Latest member
charlottedv

Share this page

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
Back
Top