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

Darkcloud617

New Member
Joined
Sep 7, 2017
Messages
38
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).

Code:
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

Code:
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
"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?
 
Upvote 0
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,

Code:
[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

Code:
Public NextTime As Date
 
Last edited:
Upvote 0
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.

Code:
[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

Code:
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.
 
Last edited:
Upvote 0
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.

Code:
[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)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top