Hi all,

I have found some code on the web which runs a routine every 1 second (using an API call: winmm.dll).
THis code runs great when a user isnt typing in a cell in Excel. When a user does, an error occurs.

Does anyone know a solution to this problem, or has a better way to let a routine change a cell (in a certain interval), while the user is typing in another cell?

thanks in advance
max

Hi Max

I´m doing by this way
This does not make error while typing in a cell.
It waits until typing is finished

Sub StartAgain()
Start = Now + TimeSerial(0, 0, 1)
Application.OnTime Start, "StartAgain"
'*** Doing something
Debug.Print Time
End Sub

Sub StopStart()
On Error Resume Next
Application.OnTime Start, "StartAgain", , False
End Sub

-vemaju

thank you for your reply. Vemaju, I know this property of VBA.

What I am trying to do is making a timer which counts down while a user is editing cells (this timer has to run continuously). The .OnTime statement, as you said, updates when a user is done with entering a value, which is not what i want

Are there any other suggestions?

thanks in advance
max

I use SetTimer and KillTimer API functions with a callback to my timer procedure. I haven't seen it raise an error before so you may want to use that instead

eg:

gcianfanelli,

I used your code and adjusted it. See below:

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

Private mTimerId As Long

Public Sub beginTimer()
  Dim timerInterval As Long

  timerInterval = 1000 'milliseconds

  mTimerId = SetTimer(0, 0, timerInterval, AddressOf yourTimerProcedure)
End Sub

Public Sub endTimer()
  KillTimer 0, mTimerId
End Sub

Public Sub yourTimerProcedure(hWnd As Long, message As Long, idTimer As Long, dwTime As Long)
  'your code here, eg:
sheet1.range("A1").value = Sheet1.Range("A1").Value + 1
' Static counter As Long
' counter = counter + 1
' Debug.Print counter
End Sub
'
Try running this code. If you aren't typing in a cell, the code works fine.
However, if you select a cell while the code is running,
Excel crashes.

I want to be able to change cell content whilst the counter keeps running. Any way
to do that?

thanks

-Max

Maybe it can't be done this way, although you could cheat and write the text to a floating text frame which is set to be the same size etc as cell A1. This still would, however, mean that the currently edited cell's contents would be "committed", and would make data entry quite difficult

Do you really have to update a specific cell every second? If it is for information only, couldn't you use a non-modal floating userform instead? Or even a custom toolbar control..

i put a label on the worksheet, and i can type in cells now. However, I think this is not a problem with code
but i think its Excel that somehow disables screenrefreshing when you type in a cell.