CODEWITHSUNDEEP
excelvbatimerdoevents
Calvin Dahms
Calvin Dahms

Reputation: 21

How do I do a Do Event timer in vba excel while still being able to change values in my worksheet?

I have a timer I am actively running through a DoEvents function. The problem is that every thirty secs I want to manually put some temperature values in my worksheet. However, if I do that while the event is running I get:

Run-time error '1004': The specified dimension is not valid for the current chart type.

Some thoughts: I was going to try turning off screen updating but that would keep me from seeing the time change in real time.

Is the active event keeping me from working in my document? And if so, is there a way around this?

Here is the body of my code (I also have a sub for the stop and reset timer but I don't think that's pertinent):

Sub START_TIMER()
Dim sh As Worksheet
Set sh = ThisWorkbook.ActiveSheet

sh.Range("AI1").Value = "Start"

If sh.Range("AI2").Value = "" Then
sh.Range("AI2").Value = Now
sh.Range("AL2").Value = Now
End If

x:
VBA.DoEvents

If sh.Range("AI1").Value = "Stop" Then Exit Sub

sh.Range("AI3").Value = Now
sh.Range("AL3").Value = Now

    If sh.Range("AL4").Value > TimeValue("00:00:29") Then
        sh.Range("A17").Interior.Color = vbYellow
        sh.Range("AL2").Value = Now
        Beep

    Else
         sh.Range("A17").Interior.Color = vbWhite

    End If

GoTo x

If sh.Range("AL4").Value = "0:00:30" Then
    sh.Range("AL2").Value = Now
End If

End

End Sub

Upvotes: 2

Views: 1051

Answers (1)

Calvin Dahms
Calvin Dahms

Reputation: 21

I figured it out!

If I just put an on error resume next then it runs well enough to work for my needs.

Upvotes: 0

Related Questions