Runtime 50290 error When trying to enter Time value in a Range

[kevinvalerio]

I am building a userform where someone can track the time it takes to do something and compare that to how long it should have taken them. My code runs smoothly most of the time but once in a while I will encounter a Runtime 50290 error when trying to enter a time value in a range.

Code:


Dim TargetRow As Integer
TargetRow = Sheets("Engine").Range("B3").Value + 1 'engine B3 is current amount of entries in form

Dim TargetRow2 As Integer
TargetRow2 = Sheets("Engine").Range("I3").Value + 1

txt_Material1.Value = Sheets("RPRT").Range("C5").Offset(TargetRow - 1, 0).Value
txt_Hose1.Value = Sheets("RPRT").Range("C5").Offset(TargetRow - 1, 1).Value
txt_Quantity1.Value = Sheets("RPRT").Range("C5").Offset(TargetRow - 1, 2).Value

Runtime 50290 error occurs at line of code below

'//////////////////////////////

Sheets("RPRT").Range("C5").Offset(TargetRow - 1, 3).Value = "'" & Format(Now, "hh:mm:ss")

'//////////////////////////////

lbl_PlanTime = Format(Sheets("RPRT").Range("C5").Offset(TargetRow - 1, 6).Value, "hh:mm:ss")
lbl_Timer = "00:00:00"
timer = True

Do While timer 'allows the timer to start counting
Application.Wait (Now + #12:00:01 AM#)
DoEvents
lbl_Timer = Format(DateAdd("s", 1, lbl_Timer), "hh:mm:ss")
Loop

End Sub



Not sure what is wrong with the code because it does work most of the time. Looking for any advice to fix this or work around it.

Thank you for your help!

[Leith Ross]

Hello kevinvalerio,

Rows should be dimension as a LONG type instead of an INTEGER. The reason is the highest integer row number is 65535. This seems to fit with the intermittent behaviour.

[Leith Ross]

Hello kevinvalerio,

I noticed you are using Target in your code. Generally this is used with Worksheet Event code. Is your macro in either a Worksheet Selection or Change event?

[Paul_Hossler]

@
kevinvalerio

Welcome to the forum

Take a minute to read the FAQs linked in my sig, especially the part about using CODE tags

[kevinvalerio]

I changed the code as follows below;

Dim TargetRow As Long
TargetRow = Sheets("Engine").Range("B3").Value

'stores data for finish time

With Sheets("RPRT").Range("C5").Offset(TargetRow, 4)
**.Value = Now**
.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
End With
'code keeps crashing here "Runtime 50290"

The bolded out line is where I'm getting the error now. It seems like vba is having trouble recording a Now value.

[kevinvalerio]

The TargetRow is just for vba to know how many rows to offset for userform data entry. This macro enters data into a cell on a worksheet

[Leith Ross]

Hello kevinvalerio,

If I could see your workbook it would help me to better troubleshoot this problem. Would that be possible?

[kevinvalerio]

How can i get you the workbook??

[Leith Ross]

Hello kevinvalerio,

You should be able to post a copy. At the bottom right hand corner of the Reply Window, you should see a button "Go Advanced". Click that button and scroll down to the section "Attachments". To the right you will see "Manage Attachments". Follow the directions for uploading your file.

[kevinvalerio]

Schedule sheet is full of order numbers and quantities (all random)

Timer sheet keeps track of the start and finish times and duration for all the orders.

Confirmation Form just asks user to confirm they are working on the correct order.

Timer Form gives user a visual of how long it is taking them in real time.

Error occurs every so often when the user clicks the Finish Button on the TimerForm.


I just tested it right now and it worked good for 30 orders straight and on the 31st is crashed.

[Leith Ross]

Hello kevinvalerio,

Not sure if it just on my end but when I click the link to the workbook nothing happens. If you click on the link does it download for you?

[kevinvalerio]

When i click the file attachment it automatically downloads a copy of the file.

Is there any other way that i can get it to you?

[kevinvalerio]

Try the file attachment on the other website

https://www.excelforum.com/excel-programming-vba-macros/1257175-runtime-50290-error-when-trying-to-enter-time-value-in-a-range.html#post5030585

[Leith Ross]

Hello kevinvalerio,

Okay I got the file from the other site. I found the problem. I will fill you in tomorrow about the cause.

[macropod]

kevinvalerio: You evidently started a discussion on the same topic - at about the same time as you first posted here - @ https://www.excelforum.com/excel-programming-vba-macros/1257175-runtime-50290-error-when-trying-to-enter-time-value-in-a-range.html. That is what is known as cross-posting. Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[kevinvalerio]

Hey were you able to come up with a solution to the bug?

[Leith Ross]

Hello kevinvalerio,

The bug was caused by the ActiveX buttons on the UserForm and Application.OnTime. Clicking and holding a button passed the default time setting causes the Application.OnTime callback to timeout. I created a timer that is independent of Excel.

I have made the needed changes and tested the code many many times for errors. The attached workbook has all the code changes shown added to it.

Module VBA_Timer


Global Paused As Boolean
Global TimerOff As Boolean


'// Conditionally compile the API call for either 64 bit Windows or 32 bit Windows.
#If VBA7 Then
Private Declare PtrSafe Function GetTickCount Lib "Kernel32.dll" () As Long
#Else
Private Declare Function GetTickCount Lib "Kernel32.dll" () As Long
#End If


Sub StartTimer(ByRef oleObj As Object)


Paused = False
TimerOff = False

'// Count from 0:00:00 to 8:00:00 hours.
Call vbaUpDownTimer(0, 28799, xlUp, oleObj)

End Sub


Sub StopTimer()


TimerOff = True

End Sub


Sub PauseTimer()


Paused = True

End Sub


Sub ResumeTimer()

Paused = False

End Sub


Sub vbaUpDownTimer(ByVal secsStart As Long, ByVal secsEnd As Long, ByVal Direction As Long, ByRef oleObj As Object)


Dim cnt As Long
Dim Secs As Long

If Direction = xlUp Then Secs = secsStart
If Direction = xlDown Then Secs = secsEnd

Do
oleObj.Caption = Format(Secs / 86400, "h:MM:ss")

'// Pause timer?
While Paused = True: DoEvents: Wend

'// Stop timer?
If TimerOff = True Then Exit Do

cnt = GetTickCount + 1000

'// One second delay.
While GetTickCount <= cnt: Wend

If Direction = xlUp Then
If Secs >= secsEnd Then Exit Do Else Secs = Secs + 1
End If

If Direction = xlDown Then
If Secs <= secsStart Then Exit Do Else Secs = Secs - 1
End If

DoEvents
Loop

End Sub


TimerForm


Option Explicit


Dim s As Integer


Sub SaveTimes()


'Engine holds the values for quantity of current entries on different sheets helps with offsetting


Dim TargetRow As Long
TargetRow = Sheets("Engine").Range("B3").Value

'stores data for finish time

With Sheets("Timer").Range("C5").Offset(TargetRow, 4)
.Value = Now
.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
End With
'code keeps crashing here "Runtime 50290"

'stores data for actual duration of the work order
Sheets("Timer").Range("C5").Offset(TargetRow, 5).Value = "'" & Format(lbl_Timer, "hh:mm:ss")


'moves selection to the row where the next order will be placed
Sheets("Timer").Range("C5").Offset(TargetRow + 1, 0).Select

'timestamp for finish order
With Sheets("Timer").Range("C5").Offset(TargetRow - 1, 3)
.Value = Now
.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
End With
' Runtime 50290 error crashes here

'there is a timer on this userform and a=false stops the timer before the userform is closed
'timer = False

TimerOff = True
Unload TimerForm

'if timer value is greater than planned time with 15% buffer then operator will have to input reason for downtime




End Sub




Private Sub UserForm_Activate()


'makes userform pop up in a specific location each time

Me.StartUpPosition = 0
Me.Top = Application.Top + 25
Me.Left = Application.Left + Application.Width - Me.Width - 25


'sets variables so that excel can find the exact rows to put the data in
Dim TargetRow As Long
TargetRow = Sheets("Engine").Range("B3").Value + 1

Dim TargetRow2 As Long
TargetRow2 = Sheets("Engine").Range("I3").Value + 1


'automatically populates information for material, quality in the userform
txt_Material1.Value = Sheets("Timer").Range("C5").Offset(TargetRow - 1, 0).Value

txt_Quantity1.Value = Sheets("Timer").Range("C5").Offset(TargetRow - 1, 1).Value

'starts off the timer at 0:00:00 and stops at 8:00:00.
Call StartTimer(Me.lbl_Timer)

End Sub


Private Sub cmd_Pause_Click()

Paused = True

End Sub


Private Sub cmd_Resume_Click()


Paused = False


End Sub


Private Sub cmd_Finish_Click()

Call SaveTimes

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


Call SaveTimes

End Sub


Merry Christmas and Happy New Year!

Nollaig Chridheil agus Bliadhna Mhath Ùr!

[kevinvalerio]

Problem was solved. I have been using the new timer in my spreadsheet for about 2 weeks now and haven't any issues. The timer being independent from excel has been a major help because now I can edit the spreadsheet if needed without the timer pausing out on me. Thank you so much Leith Ross, you are a life saver!!

[Leith Ross]

Hello Kevin,

You're welcome. Thanks for the update. I am happy to know everything is stable now.

[kevinvalerio]

Hello @Leith Ross,

First I want to thank you again. The new timer has been working great still.

I just wanted to ask you if there is a way to change the color of the font of the new timer, after it reaches a specific time.

example: if a job takes 3 min. to complete but the timer surpasses 3 min. I want the timer font to turn red at 3 min. and 1 sec.

I was able to get it to work with the old timer, but I don't know where to put the if statement to get it to work now.

Thanks again!