« Create Mdb Tables in VBA
Different approaches to retrieve data from underlying workbooks »

Blinking Cells

23rd January 2006, 08:31 pm by Dick Kusleika

Created by Bill Manville

To create a blinking cell:

If you define a new Style (Format / Style / Flash/ Add ) and apply
that style to the cells you want to flash, paste the following code
into a module sheet and run the procedure Flash from Auto-Open if
desired you will get the text flashing alternately white and red.

Dim NextTime As Date
 
Sub Flash()
  NextTime = Now + TimeValue("00:00:01")
  With ActiveWorkbook.Styles("Flash").Font
    If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
  End With
  Application.OnTime NextTime, "Flash"
End Sub
 
Sub StopIt()
  Application.OnTime NextTime, "Flash", schedule:=False
  ActiveWorkbook.Styles("Flash").Font.ColorIndex = xlAutomatic
End Sub

Editor's Note: For the love of everything holy, don't do this.

Category: Excel Experts E-Letter  |  Comment (RSS)  |  Trackback

7 Comments

  1. Andrew:

    I think I should add this to all of my workbooks (Aaarrrggghhh) ;-)

    23 January 2006, 9:20 pm

  2. Tushar Mehta:

    The traditional way to implement blinking is with code that does the work on a cell by cell basis. Bill's solution improves on it dramatically by use a style.

    The down side, of course, is that if one wants to change the emphasis method, one must modify VBA code. Another approach that moves the formatting responsibility out of the VBA is at http://www.tmehta.com/tmxl/dbfind_byid.asp?id=14

    One can implement all kinds of neat -- including some useful -- effects with that technique. Examples include an arrow cycling through some number of cells and a blinking point in a chart!

    23 January 2006, 9:40 pm

  3. J-Walk:

    I prefer the low-tech approach: Just open and close your eyes real fast.

    24 January 2006, 7:40 am

  4. Ken Puls:

    Hi guys,

    A way to change the method without actually having to modify VBA code would be to set up two more styles. A "Flash_Red" style with red font, and a "Flash_White" style with a white font. The VBA could then be adjusted to:

    Sub Flash()
    NextTime = Now + TimeValue("00:00:01")
    With ActiveWorkbook.Styles("Flash").Font
    If .ColorIndex = ActiveWorkbook.Styles("Flash_Red").Font.ColorIndex Then _
    .ColorIndex = ActiveWorkbook.Styles("Flash_White").Font.ColorIndex Else .ColorIndex = _
    ActiveWorkbook.Styles("Flash_Red").Font.ColorIndex
    End With
    Application.OnTime NextTime, "Flash"
    End Sub

    Personally, I think I'd opt for John's method though. :)

    24 January 2006, 10:23 am

  5. supert8ch:

    I need this function but want it to flash all the time. Everytime someone opens the document that is. I have played with the code but after I save any changes to the document the items I wish to still flash stop. I have to go in to macros and manually start the flash. Where am I messing up? I am new to this stuff in excel.

    15 July 2008, 9:56 am

  6. Jon Peltier:

    Supert8ch:

    Flashing cells? I'm glad I don't have to use any of your apps.

    16 July 2008, 6:05 am

  7. Alan:

    Guys, I always get an error msg on this one - Application.OnTime NextTime, "Flash"
    - that he cannot find the macro Flash... can you help? Thx

    21 August 2008, 1:20 am

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment