Excel can't find macro

D

dhstein

This is a strange and difficult issue. I'm running Application.OnTime to
close a shared workbook after 1 hour of inactivity. This prevents a user
from locking up the workbook. When testing this on my home computer, it
works well. However on the system at work there is a problem. It seems if
the user is another workbook, Excel can't find the "SaveAndClose " macro (I
don't recall the exact error message - something like "unable to locate macro
"SaveAndClose". I do the same thing at home and it works fine. Any ideas on
what could be causing this? The macro is shown below and is in the
"ThisWorkbook" code. The SaveAndClose macro is in a general module. Thanks.

Private Sub Workbook_Open()
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conflicting VBA Coding 7
Impossible? - Close code 1
Time close and save 0
Close help 2
Timer question 5
Help! Combine Macros 2
RunWhen error 4
Code Stopped 2

Top