Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Turn off Application.OnTime...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone else?.....

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •