Run Time Error '13': Type Mismatch on time calculation

[Darkcloud617]

Hello,

I found a code online that is very useful for one of my purposes but it is throwing a Mismatch error when I am attempting to use it. The code is placed in 'ThisWorkbook' to start calculating the time someone is in a workbook- to eventually kick them out automatically (with a different code in the Module).

Private Sub Workbook_Open()
'Set StartTime when the workbook is opened.
StartTime = Timer
'Schedule a call to CheckTime in the future to check elapsed idle time.
Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"
End Sub

and this line is throwing the Mismatch error

Application.OnTime (Now + TimeValue(TimeCheckDelay)), "CheckTime"

I am at a loss as I have tried to correct the "" (I thought the quotes may be causing it) and attempted to rework it a bit but was not successful. To give a broad view, this code starts counting when a workbook is opened, counts down 58 minutes, if workbook still opened at 58 minutes then close the workbook without saving. I believe the code above is just starting the timer and referring back to check in 10 minutes if its still opened. Any help you can provide will be very helpful.

 

[Joe4]

"TimeCheckDelay" seems to be a variable of sorts, but I don't see it being defined or set anywhere.
Is there other code that defines/sets this?

 

[AlphaFrog]

TimeCheckDelay is a variable that has yet to be declared and set. It throwing a type mismatch error because it cannot be converted into a TimeValue.

Put this in the ThisWorkbook code module,

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=green]'When the workbook is opened, set NextTime value[/COLOR]
    NextTime = Now + TimeValue("58:00:00")
    [COLOR=green]'Schedule a call to CheckTime in the future to check elapsed idle time.[/COLOR]
    Application.OnTime [COLOR=darkblue]Next[/COLOR]Time, "CheckTime"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
    [COLOR=green]'UnSchedule a call to CheckTime in the future if any[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] Next
    Application.OnTime NextTime, "CheckTime", Schedule:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Declare this at the top of any standard code module e.g. Module1

Public NextTime As Date

 

[AlphaFrog]

EDIT of previous post #3

TimeCheckDelay is a variable that has yet to be declared and set. It throwing a type mismatch error because it cannot be converted into a TimeValue.

Put this in the ThisWorkbook code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=green]'Set StartTime when the workbook is opened.[/COLOR]
    StartTime = Timer
    [COLOR=green]'When the workbook is opened, set NextTime value[/COLOR]
    NextTime = Now + TimeValue("58:00:00")
    [COLOR=green]'Schedule a call to CheckTime in the future to check elapsed idle time.[/COLOR]
    Application.OnTime [COLOR=darkblue]Next[/COLOR]Time, "CheckTime"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
    [COLOR=green]'UnSchedule a call to CheckTime in the future if any[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] Next
    Application.OnTime NextTime, "CheckTime", Schedule:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Declare this at the top of any standard code module e.g. Module1

Public NextTime As Date

The Before_Close procedure is to unschedule the pending OnTime event before closing the workbook. Otherwise, if the user closes the workbook, but leaves the Application running, the workbook will automatically reopen when the OnTime event is scheduled.

 

[JoeMo]

EDIT of previous post #3

TimeCheckDelay is a variable that has yet to be declared and set. It throwing a type mismatch error because it cannot be converted into a TimeValue.

Put this in the ThisWorkbook code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=green]'Set StartTime when the workbook is opened.[/COLOR]
    StartTime = Timer
    [COLOR=green]'When the workbook is opened, set NextTime value[/COLOR]
    [B][COLOR=#ff0000]NextTime = Now + TimeValue("58:00:00")[/COLOR][/B]
    [COLOR=green]'Schedule a call to CheckTime in the future to check elapsed idle time.[/COLOR]
    Application.OnTime [COLOR=darkblue]Next[/COLOR]Time, "CheckTime"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[/QUOTE]

I think the red highlighted line will produce a type mismatch error as well. Replace with:

NextTime = Now + TimeSerial(0,58,0)

 

[AlphaFrog]

Good catch JoeMo. It should have been...

NextTime = Now + TimeValue("00:58:00")

 

[Darkcloud617]

This is absolutely wonderful. That last code worked like magic. Thank you both for the assistance.

 

[JoeMo]

This is absolutely wonderful. That last code worked like magic. Thank you both for the assistance.

You are welcome - glad we could help.