Turn off Application.OnTime...

[charlie79]

I have an excel app (xl2k) that is refreshing every five secs, at one point, it's possible that I will present the user with a userform, which is waiting for a response from the user. When the userform is shown, five seconds later if the user hasn't responded to the form, I get a compile error. It says it can't show the form, since it's already shown. I've tried using:
Application.EnableEvents = False
but that doesn' seem to do anything. Any ideas?

[charlie79]

Anyone else?.....

[Tom Schreiner]

Hi
Post your timer sub and I'll give you a line to put in your usereform initialize event which will turn it off until the form is closed.
I don't know why the code is trying to reload your form???
Post some of your code...
Tom

[charlie79]

Sub Refresh()
'On Error GoTo Refresh_err
Const ForAppending = 8
Dim f, fs

Sheet1.Unprotect "hmx"
Call ADOImportFromAccessTable(strDBPath, "V_EXCEL_EXPORT", Cells(19, 1))
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.opentextfile(strLogPath, ForAppending, True, TristateUseDefault)
Set f = fs.GetFile(strLogPath)

If f.Size > 250000 Then
fs.copyfile strLogPath, strLogPath & Date & ".log"
fs.deletefile strLogPath
End If

Set f = fs.opentextfile(strLogPath, ForAppending, True, TristateUseDefault)
f.Write Date & " - " & Time & " - " & "Everything OK" & vbCrLf
f.Close

Sheet1.Protect "hmx"
Refresh_err:
Exit Sub
End Sub

Sub RefreshCurrent()
'On Error GoTo RefreshCurrent_Err
Application.OnTime Now + TimeValue("00:00:05"), "RefreshCurrent"
Sheet1.Unprotect "hmx"
Range("G8:S10").Calculate
Range("A8:A8").Calculate
Range("A30:A30").Calculate
Range("B74:B74").Calculate
Range("B76:B76").Calculate
Range("B78:B78").Calculate
Call Refresh
Sheet1.Protect "hmx"
RefreshCurrent_Err:
Exit Sub
End Sub



ADOImportFromAccessTable_Err:

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.opentextfile(strErrLogPath, ForAppending, True, TristateUseDefault)
Set f = fs.GetFile(strErrLogPath)

If f.Size > 250000 Then
fs.copyfile strErrLogPath, strErrLogPath & Date & ".old"
fs.deletefile strErrLogPath
End If

Set f = fs.opentextfile(strErrLogPath, ForAppending, True, TristateUseDefault)
f.Write Date & " - " & Time & " - " & Err.number & " - " & Err.Description & vbCrLf
f.Close

'Sheet1.Calculate
'myType = TypeName(Cells(8, 1).Value)
CheckCell = IsError(Cells(8, .Value)
If CheckCell = True Then
If frmClose.Visible = False Then frmClose.Show 'By checking if it's visible... you get the picture
End If
Resume ADOImportFromAccessTable_exit
End Sub