API Timer Function Issue

[Seth]

I am using the API timer functions in an Excel XP
project. I use the timer to refresh information every 1
second on a userform. When the user form initializes I
start the timer, and on terminate I stop the timer.

The problem is that when I return to Excel I am not able
to use the mouse to click on cells or toolbar buttons. I
found that hitting ctrl-O to open the File Open dialog
will "wake" Excel up (I can select cells and click
toolbar buttons again once I close this dialog).

Does anybody know what is going on? I only get this
problem when I use the API timer functions in conjunction
with a UserForm.

Code is shown below

Thanks,

Seth


Public Declare Function SetTimer Lib "user32" (ByVal hwnd
As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As
Long
Public Declare Function KillTimer Lib "user32" (ByVal
hwnd As Long, ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Private x As Integer

Sub StartTimer()
' I call this routine from a userform Initialize event
TimerSeconds = 1 ' how often to "pop" the timer.
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&,
AddressOf TimerProc)
Debug.Print "Started API timer, ID# " & TimerID
End Sub

Sub EndTimer()
' I call this procedure from a userform Terminate event
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)

' My code here
' It all works fine
' I run the EndTimer code on the Terminate event of a
userform
' When i do this, i can't click on anything in Excel (see
problem described above)

End Sub

 

[Jan Karel Pieterse]

Hi Seth,

I am using the API timer functions in an Excel XP
project. I use the timer to refresh information every 1
second on a userform. When the user form initializes I
start the timer, and on terminate I stop the timer.

The problem is that when I return to Excel I am not able
to use the mouse to click on cells or toolbar buttons. I
found that hitting ctrl-O to open the File Open dialog
will "wake" Excel up (I can select cells and click
toolbar buttons again once I close this dialog).

Excel isn't very fond of the API timer. It tends to get hold of Excel
at moments that Excel doesn't want it to.

E.g.: one can set the API timer to run a sub which will try to do
things to Excel, for instance save a workbook. If those actions are
actions normally not permitted in the current state (e.g. whilst in
print preview!), Excel might crash hard.

That being said, is it not possible to use the Ontime method instead?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com