Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook Events (Excel 97)

    Hi all,

    Looking at the VBA-editor, I have the following code in "This Workbook" :

    Private Sub Workbook_Open()
    Application.Run "FlashCell"
    End Sub

    In a "Module1" I have the following code (credits to 'macropod' message 227472 <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> ):

    Sub FlashCell()
    On Error GoTo EindeMacro
    Range("A1").Calculate
    Application.OnTime Now + TimeValue("0:00:01"), "FlashCell"
    Exit Sub
    EindeMacro:
    End Sub

    This all works fine : when I open the XLS-file : the "A1" starts flashing.
    BUT, when I try to close the XLS-file, it always re-opens. I have to manually shut down the Sub "FlashCell" , otherwise the file keeps re-opening.

    Can anyone help me with this ? <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>
    MTIA

    Walter

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Events (Excel 97)

    You must terminate the timer before closing the file using the Before_close event in the thisworkbook module.

    Change your code in module1 to:

    Dim dNextTime as Double

    Sub FlashCell()
    On Error GoTo EindeMacro
    Range("A1").Calculate
    dNextTime=Now + TimeValue("0:00:01")
    Application.OnTime dNextTime, "FlashCell"
    Exit Sub

    Sub EndIt()
    Application.OnTime EarliestTime:=dNexttime, Procedure:="FlashCell", Schedule:=False
    End Sub

    And in the Thisworkbook module:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EndIt
    End Sub

    EindeMacro:
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Events (Excel 97)

    Hi, Jan Karel !

    I changed my code as you indicated and it worked !

    Thank you.

    Walter

Posting Permissions

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