OnTime macro stops running when Excel minimized

north

New Member
Joined
Dec 12, 2013
Messages
7
Hi. I am using Excel2010.

My sheet displays values collected from a database via an AddIn.
The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute.
When the values exceed a certain limit, a sound is played from a custom Alarm function.

This all works as long as the excel window is active.

But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.

Is there a workaround to this?

Appreciate all answers.
 

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
Hi, it might be helpful to post your code, but anyway, maybe the SendKeys doesn't work when the workbook is not active? I suggest you do a little experiment to check that. If that's the problem, then you need to run the add-in function using a direct call in your code. This is much nicer anyway, SendKeys is generally quite bugy.
 

north

New Member
Joined
Dec 12, 2013
Messages
7
In Sheet1:
Code:
Private Sub Workbook_Open()
Run "TimerSet"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
In module:
Code:
Sub TimerSet()
Application.OnTime Now + TimeValue("00:01:00"), "TimerSet"
Application.WindowState = xlNormal
Application.SendKeys ("^G")
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime TimeValue("00:01:00"), "TimerSet", , False
End Sub


Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\beep.wav" 
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function
 

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
I would try it with a direct call to TimerSet and not use the Run method.

Also, StopTimer will not kill the timer. You need to store the EarliestTime in a Global variable in the SetTimer routine and use that as the EarliestTime in StopTimer as well. It must have the exact same time in both calls.
 

north

New Member
Joined
Dec 12, 2013
Messages
7
Hi, it might be helpful to post your code, but anyway, maybe the SendKeys doesn't work when the workbook is not active? I suggest you do a little experiment to check that. If that's the problem, then you need to run the add-in function using a direct call in your code. This is much nicer anyway, SendKeys is generally quite bugy.

Thank you for your reply, Cool blue.

I think you are right about SendKeys being the problem. I made a test sheet with a NOW() function and Calculate instead of SendKeys in the TimerSet sub.
Code:
Sub TimerSet()
Application.OnTime Now + TimeValue("00:00:30"), "TimerSet"
Calculate
End Sub
When the NOW() function exceeded my set "date limit" the alarm sounded, even with Excel minimized.

I will look into the option of running the update from the AddIn without use of SendKeys as you suggested.

Thanks again.
 

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
Just try like this...

Code:
Private Sub Workbook_Open() 
    TimerSet
End Sub  
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    StopTimer 
End Sub
And, as I said, the timer will not be killed by your current code.
The spreadsheet will keep re-opening if you close it.
You need to do it like this...

Code:
Public nextTime as Double

Sub TimerSet() 
    nextTime=Now + TimeValue("00:00:30")
    Application.OnTime nextTime, "TimerSet" 
    Calculate 
End Sub

Sub StopTimer() 
    Application.OnTime nextTime, "TimerSet", , False 
End Sub
I don't know why people put the Resume Next before killing the timer but I think its a bad idea. You should not get an error unless you do something wrong and you need to know about it.

But the fundamental problem is probably SendKeys
 
Last edited:

north

New Member
Joined
Dec 12, 2013
Messages
7
Thank you for your insight, Cool blue. I'll apply your improvements.
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top