Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    stoping code (2003)

    I am using something similar to <post#=265065>post 265065</post#>, I have to following:

    <pre>Sub FlashCell()
    Range ("C4").Calculate
    Application.OnTime Now + TimeValue("0:00:01"), "FlashCell"
    End Sub</pre>


    I am starting the with the "Workbook_Open" procedure.

    When I only close this workbook, it tries to reopen it, but when I close Excel, it closes.

    How can I fix this problem??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stoping code (2003)

    In the first place, I strongly recommend *not* to use this. Flashing cells are dangerous and a nuisance.

    If you really must, do something like this:

    Public blnStop As Boolean

    Sub FlashCell()
    Range("C4").Calculate
    If Not blnStop Then
    Application.OnTime Now + TimeValue("0:00:01"), "FlashCell"
    End If
    End Sub

    Sub StopFlashing()
    blnStop = True
    End Sub

    Call StopFlashing from the Workbook_BeforeClose event. You may also want to assign the StopFlashing macro to a command button, so that the user can end the flashing by clicking the button.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    Thanks to both of you.

    I don't like having to do this, but it seems to be the only way I can get people to notice the cell that contains "Special Condition" information, if there is any. If you have any other ideas, would gladly like to hear them.


    thanks,
    jackal

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    Try this untested code. First, change your flash routine to this:

    <code>
    Dim dtNextOnTime As Date

    Sub FlashCell()
    Range("C4").Calculate
    dtNextOnTime = Now() + TimeValue("0:00:01")
    Application.OnTime dtNextOnTime, "FlashCell"
    End Sub
    </code>


    You must also change your WorkBook_Open routine to use the dtNextOnTime variable.

    Then add this workbook BeforeClose routine:

    <code>
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
    Application.OnTime dtNextOnTime, "FlashCell", False
    Application.EnableEvents = True
    End Sub
    </code>
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    I have edited my code to include a couple of lines that should prevent a possible timing proble. Still haven't tested any of it.

    I agree with Hans that flashing a cell like is extremely irritating and I would avoid it at all costs. I would much prefer some other way of hiliting the cell, like very noticable background and font colors. Like maybe a 14 point bold red font on a yellow background.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    Well, I have tried bright colors and different size text. I need something that will definately stand out. And if I make the text too big it effects the printout.

    I am still getting the same problem as i started with, when I close this workbook, it asks me if I want to enable/disable macos, just like i was opening it again. Here is exactly what I have:

    In Module1:
    <pre>Dim dtNextOnTime As Date
    Sub FlashCell()
    Range("D56").Calculate
    dtNextOnTime = Now() + TimeValue("0:00:01")
    Application.OnTime dtNextOnTime, "FlashCell"
    End Sub</pre>


    In "ThisWorkbook":
    <pre>Private Sub Workbook_Open()
    Run "FlashCell"
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
    Application.OnTime dtNextOnTime, "FlashCell", False
    Application.EnableEvents = True
    End Sub</pre>


    Maybe I am missing something??
    thanks,
    jackal

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    OK, put this code in Module 1:

    <code>
    Option Explicit

    Dim dtNextOnTime As Date
    Sub FlashCell()
    Range("D56").Calculate
    dtNextOnTime = Now() + TimeValue("0:00:01")
    Application.OnTime dtNextOnTime, "FlashCell"
    End Sub


    Public Sub StopFlash()
    Application.EnableEvents = False
    Application.OnTime dtNextOnTime, "FlashCell", False
    Application.EnableEvents = True
    End Sub
    </code>

    and put this code in ThisWorkbook:

    <code>
    Option Explicit

    Private Sub Workbook_Open()
    Run "FlashCell"
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Run "StopFlash"
    End Sub
    </code>

    I have tested this and it works on my system.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stoping code (2003)

    Works for me too...

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    Attached is a copy of my test. When I exit Excel it works like it is supposed to. When you just close the workbook, it asks to enable/disable macros. Can you look and see what is different on mine?

    thanks,
    jackal

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stoping code (2003)

    Oops. The code I used (I didn't actually copy Legare's code, I typed it in myself) has
    <code>
    Application.OnTime dtNextOnTime, "FlashCell", , False
    </code>
    Note the extra comma before False. Without that extra comma, the code won't stop FlashCell.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    thanks very much to both of you!!!


    thanks,
    jackal

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stoping code (2003)

    This is exactly the kind of problem that Hans alluded to when he urged you not to use this technique. The attached should solve that problem.
    Legare Coleman

Posting Permissions

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