Timer,type mismatch error

[jp5162]

Working on a project for work. Creating a timer, 1 hr and 2 hr. Want the user to select and run either the 1hr or 2hr. There is a formula in the cell which is why there is a type mismatch.
VBA project below:

Sub srarttimer()
Application.ontime now + timevalue("00:00:01"), "nexttick"
End sub

Sub nexttick()
If sheet1.range("e4")=0 then exit sub
Sheet1.range("e4").value=sheet1.range.value- timevalue("00:00:01")

If sheet1.range("e4").value<=timevalue("00:10:00") then sheet1.shapes("texbox1").fill.forecolor.rgb=rgb(255'0'0)
Else
Sheet1.shapes("textbox1").fill.forecolor.rgb=(255, 255, 255)
End if

Starttimer
End sub


Sub stoptimer()
On In error resume next
Application.ontime.now+timevalue("00:00:01"),"nexttick, , false
End sub



The "e4" cell value is "=d4" . If I put an actual number in, 2:00:00
It works fine. I want user to select time for e4 STAT=1hr, ASAP=2hr
Novice guy here...please be kind. Lol
Thanks in advance for looking at this problem.

 

[wideboydixon]

It would help if you'd highlighted the line in error. However, I managed to get something working:

Private timerRunning As Boolean
Sub StartTimer()

timerRunning = True
Application.OnTime Now + TimeSerial(0, 0, 1), "NextTick"

End Sub
Sub NextTick()

If Sheet1.Range("E4") = 0 Then Exit Sub
Sheet1.Range("E4").Value = Sheet1.Range("E4").Value - TimeSerial(0, 0, 1)

If Sheet1.Range("e4").Value <= TimeSerial(0, 10, 0) Then
    Sheet1.Shapes("textbox1").Fill.ForeColor.RGB = RGB(255, 0, 0)
Else
    Sheet1.Shapes("textbox1").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If

If timerRunning Then StartTimer

End Sub
Sub StopTimer()

timerRunning = False

End Sub

WBD

 

[jp5162]

Its the 8th line I get run-time error 13, type mismatch

 

[MARK858]

What is

sheet1.range.value

supposed to be as you haven't tried to have it as a variable and if not it should have a syntax of something like Range("A1") and should

.shapes("texbox1").

be

.Shapes("Textbox1").

?

 

[wideboydixon]

Its the 8th line I get run-time error 13, type mismatch

Strange because I had this working without issue. Please indicate the actual text of the line that's giving a type mismatch.

WBD

 

[jp5162]

I have read there is a patch to fix the type mismatch errors in Excel. Currently running Microsoft Office professional Plus 2013.
As a novice, I might not explain it well in terms of excel lingo. What I have is a spread sheet, in Cell B4 is a specimen number, Cell C4 is a drop down pick list for STAT or ASAP. When STAT is picked Cell D4 displays 01:00:00 for one hour, ASAP- Cell D4 displays 02:00:00 for two hours. E4 Cell is equal to D4, used E4 for VBA code. Can I use D4 for VBA Coding?
When debugging I get that type mismatch error here in this line.

Sheet1.Range("E4").Value = Sheet1.Range("E4").Value - TimeSerial(0, 0, 1)

I'm going to try it on my excel at home and see if there is a difference.

 

[wideboydixon]

Check that the value in D4 / E4 is actually a time and not a string that *looks like* a time. One hour should be 1/24 = 0.0416666....

WBD

 

[jp5162]

That worked! Thanks
Now it is possible to have multiple timers in an excel. I would like to have 10-25. Would each one be in a separate module?
Also how can I reset the timers at the end of the day?